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
'<<=============


---
Regards,
Norman



"Naz" wrote in message
...
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

--

_______________________
Naz,
London