On Oct 17, 9:35 pm, "Jeff W." wrote:
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

"JLGWhiz" wrote in message

...

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." wrote:

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.

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