Hi, I am new to VBA and 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.
Bookmarks