Modify sub to work on cells with formulas

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
 
O

ozgrid.com

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.
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top