The sheet sub below works with values (1,2) input into the range IM2:IM100,
but fails if IM2:IM100 contains formulas returning the values 1,2. How can
it be modified to work with formulas? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
If Not Intersect(Target, Range("IM2:IM100")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
End If
End Sub

Use the Sheet calculate Event and hard code the ranges.

No need for VBA IMO, you should be using Conditional Formatting as this will
be dynamic with formulas or hard values.


--
Regards
Dave Hawley
www.ozgrid.com
"Max" wrote in message
...
The sheet sub below works with values (1,2) input into the range
IM2:IM100,
but fails if IM2:IM100 contains formulas returning the values 1,2. How
can
it be modified to work with formulas? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
If Not Intersect(Target, Range("IM2:IM100")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
End If
End Sub

Use the Sheet calculate Event and hard code the ranges

Could you kindly provide a working example sub incorporating the above? A
modification based on the example sub posted would be great

No need for VBA IMO, you should be using Conditional Formatting as this will
be dynamic with formulas or hard values

Agreed. But it was just a short example sub in the query posted, the actual
coverage was for 8 different fillcolors (1 - 8), far in excess of CF's 3 (or
3+1) colors