Modify sub to work on cells with formulas

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

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.
 
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
Click to expand...

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
 
Back
Top