Coloring Rows based on contents of cell A on each row

[Jeff W.]

I want to create this sub or macro that will start at row 2 and cell A
test for the a word and if this is found then select the cells on that row
from A to H and change the format adding color to the cells based on
the word found.

This is what I have so far, but it doesnt work...

It runs but it doesnt change anything, I'm lost, I'm not a vba programmer
but this does make sence to me, sort of...

Sub color_rows()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastrow
a_num = RowCount 'row index
h_num = RowCount + 7 'cell index

If Cells(RowCount) = "Open" Then ' test for the word "Open"
Cells("a_num:h_num").Select ' select cells A to H in the
current row
With Selection.Interior ' this would change the
format of the selected cells
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If

If Cells(RowCount) = "Waiting" Then ' test for the word "Waiting"
Cells("a_num:h_num").Select ' select cells A to H in the
current row
With Selection.Interior ' this would change the
format of the selected cells
.ColorIndex = 27
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If

If Cells(RowCount) = "Closed" Then ' test for the word "Closed"
Cells("a_num:h_num").Select ' select cells A to H in the
current row
Selection.Interior.ColorIndex = xlNone ' this would change the color to
none
End If
Next RowCount
End Sub

If anyone see's something wrong with this, I could sure use the help...

Thanks,

Jeff W.

 

[Guest]

These won't work becaus you are only referencing the row number. You need to
include the column you want to search. expl: Cells(RowCount, 1) would
search column A.

If Cells(RowCount) = "Open" Then
If Cells(RowCount) = "Waiting" Then

 

[Jeff W.]

I made the change you suggested but now it wont run without
Error it stops with "Type Mismatch" on the follwoing line;

Cells("a_num:h_num").Select ' select cells A to H in the

I have tried changing this to "Range" rather than "Cells" but the same error

Any ideas?

<Jeff>

 

[JW]

I made the change you suggested but now it wont run without
Error it stops with "Type Mismatch" on the follwoing line;

Cells("a_num:h_num").Select ' select cells A to H in the

I have tried changing this to "Range" rather than "Cells" but the same error

Any ideas?

<Jeff>

Keep in mind, you don't have anything controlling if the cells are
already colored. Might want to clear the colorindex of the rows at
the top of the code to ensure that everything is formatted correctly.

Sub color_rows()
Dim lastrow As Long, rowcount As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For rowcount = 2 To lastrow
With Range(Cells(rowcount, 1), _
Cells(rowcount, 8)).Interior
If Cells(rowcount, 1).Value = _
"Open" Then
.ColorIndex = 4
ElseIf Cells(rowcount, 1) = _
"Waiting" Then
.ColorIndex = 27
ElseIf Cells(rowcount, 1) = _
"Closed" Then
.ColorIndex = xlNone
End If
End With
Next rowcount
End Sub

 

[JW]

I made the change you suggested but now it wont run without
Error it stops with "Type Mismatch" on the follwoing line;

Cells("a_num:h_num").Select ' select cells A to H in the

I have tried changing this to "Range" rather than "Cells" but the same error

Any ideas?

<Jeff>

Keep in mind, you don't have anything controlling if the cells are
already colored. Might want to clear the colorindex of the rows at
the top of the code to ensure that everything is formatted correctly.
Notice that there are no selections in the code. In general,
unnecessary selections is considered bad coding practice and should be
avoided if at all possible.

Sub color_rows()
Dim lastrow As Long, rowcount As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For rowcount = 2 To lastrow
With Range(Cells(rowcount, 1), _
Cells(rowcount, 8)).Interior
If Cells(rowcount, 1).Value = _
"Open" Then
.ColorIndex = 4
ElseIf Cells(rowcount, 1) = _
"Waiting" Then
.ColorIndex = 27
ElseIf Cells(rowcount, 1) = _
"Closed" Then
.ColorIndex = xlNone
End If
End With
Next rowcount
End Sub

 

[Jeff W.]

Works great!

The colors need to change based on the word changing so I dont
know if it can make any difference whether they are already colored.

I know from programming in other applications that there are usually
more than one way to get someing done, what you have done here is
certainly cleaner and more compact than my method.

Thanks...

Jeff W.

 

[JW]

You're welcome Jeff. I would still recommend clearing the cell color
index before the loop. What is a record that was previously "Open"
now has something like "Close" (Closed without the D). That row would
remain colored like the "Open" rows, but it truly isn't. Better safe
than sorry. To clear all color index values of all rows except the
header row, use something like below:
Sub clearColorIndex()
Rows("2:65536").Interior.ColorIndex = xlNone
'or Rows("2:" & Rows.Count).Interior.ColorIndex = xlNone
End Sub

 

[Jeff W.]

Thanks, I'll do that...

<Jeff>

You're welcome Jeff. I would still recommend clearing the cell color
index before the loop. What is a record that was previously "Open"
now has something like "Close" (Closed without the D). That row would
remain colored like the "Open" rows, but it truly isn't. Better safe
than sorry. To clear all color index values of all rows except the
header row, use something like below:
Sub clearColorIndex()
Rows("2:65536").Interior.ColorIndex = xlNone
'or Rows("2:" & Rows.Count).Interior.ColorIndex = xlNone
End Sub