# Excel VB Row Color Change



## Stretch2459 (Aug 16, 2007)

I am hoping that someone can understand why this code is not working properly. What I am trying to do is have the color of an entire row change when a value has been entered into column A, but I also need this code to change the color to "No Fill" when that value has been deleted. My code is below.
Thanks

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A2:A50")) Is Nothing Then
Exit Sub
Else
DidCellsChange
End Sub

Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1:A50"

' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
Dim Cell As Object

For Each Cell In Range("A1:A50")

If Cell = "x" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.EntireRow.Interior.ColorIndex = 10
ElseIf Cell = "X" Then
Cell.EntireRow.Interior.ColorIndex = 10
ElseIf Cell = "" Then
' Otherwise, set the background to none (default).
Cell.EntireRow.Interior.ColorIndex = xlNone

End If
Next Cell

End Sub


----------



## bomb #21 (Jul 1, 2005)

Welcome to the board. 

Firstly, can you explain "not working properly"? What actually happens (or doesn't)?

Then there's your first code section:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A2:A50")) Is Nothing Then
Exit Sub
Else
DidCellsChange
End Sub

Do you have that in a worksheet module, rather than a standard module? Also the structure is flawed (*If* without *End If*) -- it really needs to be more like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A2:A50")) Is Nothing Then Exit Sub
DidCellsChange
End Sub

I'll look at it some more now & see what else I can come up with. HTH.


----------



## bomb #21 (Jul 1, 2005)

_Possibly_ all you need is this in the worksheet module (which you access by rightclicking the sheet tab and choosing "View Code" from the pop-up menu):

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A50")) Is Nothing Then Exit Sub
If Selection.Cells.Count > 1 Then Exit Sub
If Target <> "" Then
Rows(Target.Row).Interior.ColorIndex = 10
Else
Rows(Target.Row).Interior.ColorIndex = xlNone
End If
End Sub


----------



## Zack Barresse (Jul 25, 2004)

Hi there, welcome to the board!

Why not use Conditional Formatting?


----------



## Stretch2459 (Aug 16, 2007)

Thank you so much for your help.. bomb #21. That has definitely simplified the code. The code that I had pasted in was incorrect, looks like I copied the wrong sheet. The only think that I could not get to work on my version was when I cleared out the cell the row fill color would stay as green.
But your code works great. Thanks again

I did conditional formatting, but I have a large spreadsheet and I think that was bogging it down. Wanted to try a macro.
Thanks


----------



## Stretch2459 (Aug 16, 2007)

Bomb #21 -- after a little testing of your code, I noticed one little bug. If I was to fill in one cell (column A) and drag that content down to populate other cells in that column, the fill color would not take to the entire rows. The same thing would happen if I deleted a set of cells in column A. But after I fixed my original code and placed it on the sheet tab instead of in a module it seemed to work. 

Here is the ending code that worked - 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A2:A5500")) Is Nothing Then
Exit Sub
Else
DidCellsChange
End If
End Sub


Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1:A50"

' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
Dim Cell As Object

For Each Cell In Range("A1:A50")

If Cell = "x" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.EntireRow.Interior.ColorIndex = 10
ElseIf Cell = "X" Then
Cell.EntireRow.Interior.ColorIndex = 10
ElseIf Cell = "" Then
' Otherwise, set the background to none (default).
Cell.EntireRow.Interior.ColorIndex = xlNone

End If
Next Cell

End Sub

I appreciate all the help, this forum is awesome.


----------



## bomb #21 (Jul 1, 2005)

The "bug" was intentional.  I put in *If Selection.Cells.Count > 1 Then Exit Sub* because without it the code would crash _if_ an entry was made in >1 cell simultaneously.

But from what you say, you've figured a way to facilitate that scenario. Good job.:up:


----------



## Zack Barresse (Jul 25, 2004)

Stretch2459 said:


> I did conditional formatting, but I have a large spreadsheet and I think that was bogging it down. Wanted to try a macro.
> Thanks


Generally and usually VBA is much slower than traditional CF. I would suspect something else first. VBA should be used as a last resort.


----------

