Combining two Private Sub Worksheet_Change(ByVal Target As Range)

[iceblocks]

Hi, I have two Private Sub Worksheet_Change(ByVal Target As Range) that work great separately but I don't know how to combine them into one. Any help would be greatly appreciated.
Here is my two Private Sub Worksheet_Change(ByVal Target As Range):

Private Sub Worksheet_Change(ByVal Target As Range) 'this macro triggers other macros based on data being entered in to specific cells
If Target.Address = "$F$4" Then 'triggers HeaderChange Macro below if data entered or changed in cell B5 on the cover page
Call FontChangeName
Call Fontsize
End If
End Sub
Sub FontChangeName() 'this macro updates the date in the header throughout the risk assessment based on the Provider Number entered in to cell F5
Dim rng As Range
Dim rCell As Range
Set rng = Range("F26:H39")
For Each rCell In rng
If Len(rCell.Text) > 15 Then
rCell.Font.Size = 6
Else
rCell.Font.Size = 9
End If
Next
End Sub
Sub Fontsize()
'change font size when number of characters reach past 260
Dim wCell As Range
If Union(Target, Range("L10:O21")).Address = _
Range("L10:O21").Address Then
Application.EnableEvents = False
For Each wCell In Target
If Len(wCell.Text) > 250 Then
wCell.Font.Size = 7
Else
wCell.Font.Size = 9
End If
Next
Application.EnableEvents = True
End If
End Sub

Many thanks in advance.

 

[Hui]

What is wrong with the approach you presented?

Can you explain more about how and when the macro should perform

 

[NARAYANK991]

Hi ,

The comments against the macros are not matching the code within the macros ; can you clarify ?

For example , the comment against the Worksheet_Change macro mentions :

triggers HeaderChange Macro below if data entered or changed in cell B5 on the cover page

but the code within is checking for a change in cell $F$4.

Narayan

 

[Hui]

In the Font Change sub the Comment mentions past 260 Characters
But the code only checks if past 250 ?

 

[iceblocks]

Sorry, I should have been clearer in my previous post and codes. Best to ignore my previous attempt at the codes.

I am trying write a a worksheet_change sub which does two things automatically:
- change font size from 9 to 6 if cells F26:H39 contain 15 characters or more
- change font size from 9 to 7 if cells L10, L14 reach 250 characters or more

So I need the font to change as soon as a type over 250 characters in cells L10 or L14...

My understanding is that worksheet_change sub can't do two things at once, so that is why I tried to call two separate subs, but this doesn't seem to work either. Maybe I have made it more complicated than it needs to be.

Your help would be much appreciated.

 

[NARAYANK991]

Hi ,

See if this is what you wanted.

Private Sub Worksheet_Change(ByVal Target As Range)
Check1:
            If Application.Intersect(Target, Range("F26:H39")) Is Nothing Then GoTo Check2:
         
            For Each wCell In Range("F26:H39")
                If Len(wCell.Text) >= 15 Then
                   wCell.Font.Size = 6
                Else
                   wCell.Font.Size = 9
                End If
            Next
            Exit Sub
         
Check2:
            If Application.Intersect(Target, Union(Range("L10"), Range("L14"))) Is Nothing Then Exit Sub
         
            For Each wCell In Union(Range("L10"), Range("L14"))
                If Len(wCell.Text) >= 250 Then
                   wCell.Font.Size = 7
                Else
                   wCell.Font.Size = 9
                End If
            Next
End Sub

Narayan

 

[iceblocks]

Thanks Narayan. Just a slight variation, is it possible to have Range"F26:H39" change font size based on whether data in cell F4 is changed? And then cell L10 and L14 change font size when data entered are over >250 characters.

The reason why I ask is that user will enter an ID in cell F4 then the calculation will be produced in cells F26:H39, I need this to change font size if it produces characters greater than 10. Once this is done, then user need to type commentary into cell L10 and L14 and this need to change font size when characters entered exceeds 250.

Your codes above work fine for cell L10 and L14, but won't change font size for F26:H39 as those values are produced via a formula or vlookup and not manually entered.

Many thanks.

 

[NARAYANK991]

Hi ,

Just change the first line of code to :

If Application.Intersect(Target, Range("F4")) Is Nothing Then GoTo Check2:

What this will do is execute the section of code following this statement if any change is made in cell F4 , instead of the range F26:H39.

Narayan

 

[iceblocks]

You are a champ Narayan! Really appreciate your help with this. You have taught me some new tricks. Thank you.