Change event: one column of data determines text in another column 1

[larissalle]

I am trying to get one column of numbers to determine the text in another column. If column 1 = 0, then I want the same row in column 2 = "none" and it should be formatted with green as background pattern color. I have named ranges for each column and I tried to use a For...each...next statement so that for each value in column1's range that the value of column 2 would change. I can't get it to work. Can someone help?

Graphic of what I am trying to do

col1 col 2
=== =====
< 0 text = &quot;none&quot; with green background pattern color
> 0 leave cell blank, red background pattern color

 

[vbMax]

Assuming numbers are in column &quot;A&quot;...


Sub conditionalColors()

lastRow = Application.CountA(Range(&quot;A:A&quot)

For counter = 1 To lastRow
If Range(&quot;A&quot; & LTrim(Str(counter))).Value < 1 Then
Range(&quot;B&quot; & LTrim(Str(counter))).Value = &quot;none&quot;
Range(&quot;B&quot; & LTrim(Str(counter))).Interior.ColorIndex = 50
Else
Range(&quot;B&quot; & LTrim(Str(counter))).Interior.ColorIndex = 3
End If
Next counter

End Sub