Sorry to resurface this Hans,
I no longer want to call the code and would prefer the user to activate the code each time cell E3 is changed within a WS
I tried the following but its not working for me
Thank You
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wsh In Worksheets
If Not Intersect(Target, Target.Worksheet.Range("E3")) Is Nothing Then
If Len(wsh.Name) = 3 Then
With wsh.Range("G33:CD33")
.FormulaR1C1 = _
"=IF(AND(TODAY()<=R11C,TODAY()>=R12C),""Active Forecast Base Models ""&""F ""&""= ""&R16C1&"" ""&""FO ""&""= ""&R51C5,"""")"
.Value = .Value
End With
With wsh.Range("G2:CD2")
.FormulaR1C1 = _
"=IF(OR(R12C>EOMONTH(TODAY(),R3C5),R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
.Value = .Value
End With
With wsh.Range("G72:CD72")
.FormulaR1C1 = _
"=IF(OR(R12C>EOMONTH(TODAY(),R3C5),R12C<EOMONTH(TODAY(),R3C5-1)),"""",""Target >"")"
.Value = .Value
End With
End If
End If
Next wsh
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub