HI,
i am very new to Macros and VBA and i the following conditional formatting
ocde in my worsheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range(Target.Address),
Range("BG7:DD60"))

If iSect Is Nothing Or Target.Cells.Count 1 Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Value
Case Is = 0
With Target.Interior
.ColorIndex = xlNone
End With

Case Is < Range("c2")
With Target.Interior
.ColorIndex = 1
End With
With Target.Font
.ColorIndex = 10
End With

Case Is < Range("c3")
With Target.Interior
.ColorIndex = 6
End With
With Target.Font
.ColorIndex = 40
End With

Case Is = "n/a"
With Target.Interior
.ColorIndex = 15
End With

Case Is = "On hold"
With Target.Interior
.ColorIndex = 3
End With

End Select

Application.EnableEvents = True

End Sub

however the cells in the range are popoulated via the following equation:

=IF(OR(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2 ,FALSE)&"!$C9:$EC9")2),IF(OR(INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9")="n/a"),"n/a",(MAX(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56, 2,FALSE)&"!$C9:$EC9")))),MAX((INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9"))*(INDI RECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2,FALSE)&"!$ C$6:$EC$6"))))

The only way i can seem to get the macros to run is if i go into each cell
and press CNTRL+SHIFT + ENTER ( array formula)