As an alternative, would Conditional Formatting work for you?
Sub Demo()
With Range("V2:V40")
.Select
.FormatConditions.Delete
.FormatConditions.Add _
Type:=xlExpression, _
Formula1:="=NOT(ISBLANK(V2))"
With .FormatConditions(1).Interior
.ColorIndex = 44
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End With
End Sub
HTH :)
--
Dana DeLouis
Win XP & Office 2003
"Simon Lloyd"
wrote in message
...
Hi all,
I have tried to devise some code so that if a cell in a set range
contains a value or character of any kind to change to a colour, it
works but seems to loop many times until my machine freezes (i can
press escape to get out of it!) any ideas why and perhaps a nudge in
the right direction please!
Thanks,
Simon.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mycell
Dim rng As Range
Set rng = Range("V2:V40")
For Each mycell In rng
If mycell < "" Then
mycell.Select
With Selection.Interior
ColorIndex = 44
Pattern = xlSolid
PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub
--
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