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.

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.
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
> 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.
>>
>>
>>
>>

On Oct 17, 9:35 pm, "Jeff W." <part_ma...@verizon.net> 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" <JLGW...@discussions.microsoft.com> wrote in message
>
> news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
>
> > 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.

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

On Oct 17, 9:35 pm, "Jeff W." <part_ma...@verizon.net> 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" <JLGW...@discussions.microsoft.com> wrote in message
>
> news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
>
> > 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Oct 17, 9:35 pm, "Jeff W." <part_ma...@verizon.net> 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" <JLGW...@discussions.microsoft.com> wrote in message
>>
>> news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
>>
>> > 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
>

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. wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On Oct 17, 9:35 pm, "Jeff W." <part_ma...@verizon.net> 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" <JLGW...@discussions.microsoft.com> wrote in message
> >>
> >> news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
> >>
> >> > 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
> >

Thanks, I'll do that...

<Jeff>

"JW" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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. wrote:
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > On Oct 17, 9:35 pm, "Jeff W." <part_ma...@verizon.net> 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" <JLGW...@discussions.microsoft.com> wrote in message
>> >>
>> >> news:A0113FE9-7BC6-433C-83A3-(E-Mail Removed)...
>> >>
>> >> > 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
>> >
>