conditional format macro not working - almost there

[Naz]

Hi

I'm trying to do a macro to conditionally format some cells depending on
there value
this is the code i have written, but it only works if the whole range is a
certain number and not on the individual cells within the range.

Private Sub Worksheet_Change(ByVal Target As Range)

' This macro is activated everytime a change is made to this worksheet
(TestArea)


Dim r As Range

'Set the range for the formatting
Set r = Intersect(Range("Ranges1"), Target)

'If the change in the worksheet is not in the tested range, exit the macro.
If r Is Nothing Then Exit Sub

'Change to formatting of the cell that changed.
For Each cell In Range("Ranges1")

Select Case cell
Case 1
Selection.Interior.ColorIndex = 2
Case 2, 3
Selection.Interior.ColorIndex = 4
Case 4 To 6
Selection.Interior.ColorIndex = 6
Case Is > 8
Selection.Interior.ColorIndex = 8
Case Else
Selection.Interior.ColorIndex = 10
End Select


Next

End Sub

All help is appreciated

 

[Norman Jones]

Hi Naz,

Try this modification of your code:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
' This macro is activated everytime a change is made to
' this worksheet (TestArea)
Dim r As Range
Dim cell As Range

'Set the range for the formatting
Set r = Intersect(Range("Ranges1"), Target)

' If the change in the worksheet is not in the
' tested range, exit the macro.
If r Is Nothing Then Exit Sub

'Change to formatting of the cell that changed.
For Each cell In Range("Ranges1")
With cell
Select Case cell
Case 1
.Interior.ColorIndex = 2
Case 2, 3
.Interior.ColorIndex = 4
Case 4 To 6
.Interior.ColorIndex = 6
Case Is > 8
.Interior.ColorIndex = 8
Case Else
.Interior.ColorIndex = 10
End Select

End With
Next
End Sub
'<<=============

 

[JR]

....don't re-invent the wheel. Just conditionally format the cells based on
the given criteria and leave the macro out.

 

[Naz]

Not trying to re-invent the wheel, i'm going to need to use this code with
upto 20 conditions which the normal conditional format won't be able to do,
as its limited to only 4 (incl default) conditions.