VBA adjusting color of adjacent cell

[jerrykel]

I am in need of a VBA script to change an adjacent cell one of three colors. The first part of my script works fine. And this becomes my reference cell. I need the cell to the right of reference cell changed to the proper color. This is because my reference cells which are in multiple columns get erased prior to publication. I have commented out my attempts to change the color of adjacent cells. So far I seem to get the adjacent cell changed to green regardless of what color I try.

Any help would be appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
Set Crayon = Range("A1:BY21")
For Each Cell In Crayon

'Change formula cell color to match output value
If Cell.Value = "Green" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Yellow" Then
Cell.Interior.ColorIndex = 6
End If
If Cell.Value = "Red" Then
Cell.Interior.ColorIndex = 3
End If

'Change adjacent cell color to match ouput value
'If Cell.Value = "Green" Then
'ActiveCell.Offset(0, 1).Interior.ColorIndex = 4
'End If

'Change adjacent cell color to match ouput value 2nd option
'If Cell.Value = "Green" Then
'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 4
'End If
'If Cell.Value = "Yellow" Then
'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 6
'End If
'If Cell.Value = "Red" Then
'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 3
'End If

Next

End Sub

Regards,

Jerry Keller

 

[Tom Urtis]

Curious, why are you using VBA code for this when conditional formatting might do the job? You are only using 3 colors, and even version 2003 or before could accommodate this with CF.

 

[repairman615]

Maybe?

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Set</SPAN> Crayon = Range("A1:BY21")<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Crayon<br><br><SPAN style="color:#007F00">'Change formula cell color to match output value</SPAN><br><SPAN style="color:#00007F">If</SPAN> cell.Value = "Green" <SPAN style="color:#00007F">Then</SPAN><br>cell.Interior.ColorIndex = 4<br>cell.Offset(0, 1).Interior.ColorIndex = 4<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> cell.Value = "Yellow" <SPAN style="color:#00007F">Then</SPAN><br>cell.Interior.ColorIndex = 6<br>cell.Offset(0, 1).Interior.ColorIndex = 6<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">If</SPAN> cell.Value = "Red" <SPAN style="color:#00007F">Then</SPAN><br>cell.Interior.ColorIndex = 3<br>cell.Offset(0, 1).Interior.ColorIndex = 3<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#007F00">'Change adjacent cell color to match ouput value</SPAN><br><SPAN style="color:#007F00">'If Cell.Value = "Green" Then</SPAN><br><SPAN style="color:#007F00">'ActiveCell.Offset(0, 1).Interior.ColorIndex = 4</SPAN><br><SPAN style="color:#007F00">'End If</SPAN><br><br><SPAN style="color:#007F00">'Change adjacent cell color to match ouput value 2nd option</SPAN><br><SPAN style="color:#007F00">'If Cell.Value = "Green" Then</SPAN><br><SPAN style="color:#007F00">'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 4</SPAN><br><SPAN style="color:#007F00">'End If</SPAN><br><SPAN style="color:#007F00">'If Cell.Value = "Yellow" Then</SPAN><br><SPAN style="color:#007F00">'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 6</SPAN><br><SPAN style="color:#007F00">'End If</SPAN><br><SPAN style="color:#007F00">'If Cell.Value = "Red" Then</SPAN><br><SPAN style="color:#007F00">'Target.Offset(Crayon.Row - 1, Crayon.Column).Interior.ColorIndex = 3</SPAN><br><SPAN style="color:#007F00">'End If</SPAN><br><br><SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[jerrykel]

Thanks for the question. I am adding more colors soon.
I am dropping a column of formulas into the spreadsheet at multiple locations to get these colors. The formulas are column specific and compare two different columns with a third. VBA seemed to be the only way to effectively look at currently more than three dozen different columns of data scattered at varying locations in the spreadsheet. My intent is to simply drop the formula columns in place and have the spreadsheet color itself. Then remove the formula columns as they are not part of the finished spreadsheet.

 

[jerrykel]

Repairman615,
Thanks. That works perfectly. You just saved me an hour of manually coloring the spreadsheet every week.

Regards,

 

[repairman615]

Glad to help.

Welcome to the Board!