If you're using Shane's macro as a manually-run macro as he posted, you can
add a line to clear the cells colors then re-paint according to updated
values.
Sub myFormat()
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone 'added this
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub
But............how are the values in D and J changing?
Are they formula-derived?
If so you may want event code.
Post back.
Gord Dibben MS Excel MVP
Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.
:
Hi,
Here is the code you need for your example:
Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub
Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire
:
My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.
Thanks in advance