VBA Code - setting cell background color doesn't hold with different selection.

[Karenola888]

I am working with PSAT and SAT scores and am using a background color of the cell to show if they improved, met the benchmark, or did not improve, per student. The code below works well when I make a selection and apply the macro, BUT when I go to a new section to apply it - it works again but removes it from the prior selection. I tried to use conditional formatting first - but couldn't get that to do what I wanted, so I went to code. Does anyone know what is wrong? I attached a sample copy of the spreadsheet I'm working with. THANK YOU!


Sub MathColorize()
Dim rng As Range
Dim cell As Range
Dim AbCell As Long
Set rng = Selection
Cells.Interior.ColorIndex = 0
AbCell = 530
For Each cell In rng
If IsEmpty(cell.Value) = False Then
If cell.Value >= cell.Offset(columnOffset:=-1).Value And cell.Value > AbCell Then
cell.Interior.Color = 65280
Else
If cell.Value >= cell.Offset(columnOffset:=-1).Value And cell.Value < AbCell Then
cell.Interior.Color = 65535
Else
cell.Interior.Color = 255
End If
End If
End If
Next cell
End Sub

[JKwan]

this line removes all fill colors

Cells.Interior.ColorIndex = 0

I am guessing if you remove the selection fill color?


Selection.Interior.ColorIndex = 0

[Karenola888]

OMG! That was so easy and I did not see it. Thank you so much. That was it.