Hi Simon,

And to avoid unnecessary loops, try:

'===============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell

Set rng = Intersect(Target, Range("V2:V40"))

If Not rng Is Nothing Then
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Interior.ColorIndex = 44
Else
.Interior.ColorIndex = xlNone
End If
End With
Next rCell
End If

End Sub
'<<===============


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Simon,

Perhaps, better would be:

'===============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell

Set rng = Range("V2:V40")

If Not Intersect(rng, Target) Is Nothing Then
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Interior.ColorIndex = 44
Else
.Interior.ColorIndex = xlNone
End If
End With
Next rCell
End If

End Sub
'<<===============

This version removes the color if a cell's value is deleted.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Simon,

Try:
'===============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell

Set rng = Range("V2:V40")

If Not Intersect(rng, Target) Is Nothing Then
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
With .Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
End If
End With
Next rCell
End If

End Sub
'<<===============

---
Regards,
Norman



"Simon Lloyd"
wrote in message
...

Ok Ok so i'm a little numb!

Sorted it, it seems because i made it in Worksheet SelectionChange it
kept starting itself because a change had been made etc.

I would like it to run automatically when a value or character is
entered in the range, right now i have assigned it to a button but its
not ideal.

Regards,

Simon.


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread:
http://www.excelforum.com/showthread...hreadid=476074