The code that you have requires a change be made to the cell. The value of
the cell changing because the cell recalculates does not count as a change.
Normally you could look at changes in the precident cells of the calculation
but since you are using indirect that it kinda out of the question. But
looking at the code my question to you would be why not just use a regular
built in conditional format?
--
HTH...

Jim Thomlinson


"Emma" wrote:

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)