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)

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)

Do you mean instead of using VBA just use the normal conditional formatting
allowed by excel? That limits me to 3 colours and i have at least 5 that i
want to use.
Is there a macro that would do the equivalent of going into any way to go
into each cell pressing cntrl+ shift +enter? The information in the range is
going to be copy and paste elsewhere in my worksheet. So, i could apply the
conditional formatting to the pasted data, but i get the same problem in that
the macro doesn't execute on beacsue it doesn't read it as a cell change.

Thanks for your help.

"Jim Thomlinson" wrote:

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)