I have code that loops through all rows, checks the value in a specific cell
and based on that value sets the fill color for that cell. I want to set the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill color
for the entire row (beyond column N) would be better than what I have now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the cell?

Thank you,
Judy

Why aren't you using Conditional Formatting instead... it is automatic, that
is, it doesn't require you to run a macro to get your cells colored. Try
this and see what you think...

Go to your worksheet and click Format/Condition Formatting in Excel's menu
bar. Click the Add button so that there are two Conditions showing. For both
of those conditions, click the first drop down field and select "Formula
Is". Now, for Condition 1, put this formula in the second field...

=$K1="Validated"

Then click the Format button for Condition 1, click the Patterns tab on the
dialog box and pick a color from the chart to highlight the Validated rows
in. Click OK to return to the Conditional Formatting dialog box.

Now, for Condition 2, put this formula in the second field..

=$K1="Needs to be Validated"

Then click on the Format button for Condition 2, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the "Needs to be
Validated" row in. Finally, OK your way back to the worksheet. Your rows
should now be highlighted in the colors you selected and, every time you
place a value of "Validated" or "Needs to be Validated" in Column K, the row
will automatically get highlighted.

Rick


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to set
the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy

I forgot to tell you... BEFORE you go to the Format/Conditional Formatting
in Excel's menu bar, select columns A through M (if you want to apply the
formatting to the entire column) although more efficient would be to select
Column A through Column M down to the maximum number of rows you expect to
ever need. After you have made this selection... THEN you can follow the
directions in my original posting.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Why aren't you using Conditional Formatting instead... it is automatic,
that is, it doesn't require you to run a macro to get your cells colored.
Try this and see what you think...

Go to your worksheet and click Format/Condition Formatting in Excel's menu
bar. Click the Add button so that there are two Conditions showing. For
both of those conditions, click the first drop down field and select
"Formula Is". Now, for Condition 1, put this formula in the second
field...

=$K1="Validated"

Then click the Format button for Condition 1, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the Validated
rows in. Click OK to return to the Conditional Formatting dialog box.

Now, for Condition 2, put this formula in the second field..

=$K1="Needs to be Validated"

Then click on the Format button for Condition 2, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the "Needs to
be Validated" row in. Finally, OK your way back to the worksheet. Your
rows should now be highlighted in the colors you selected and, every time
you place a value of "Validated" or "Needs to be Validated" in Column K,
the row will automatically get highlighted.

Rick


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to set
the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have
now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy

Thank you very much for responding. I did try your suggestion to see how it
worked. I didn't mention in my original post that this data lives in an
Access database and is being exported out to Excel. I call an Excel macro to
format the data. Your idea would be great if the data were being updated in
the Excel spreadsheet, but since it is being updated in the database it's
better for me to format the cells once through code.

Thanks again,
Judy

"Rick Rothstein (MVP - VB)" wrote:

I forgot to tell you... BEFORE you go to the Format/Conditional Formatting
in Excel's menu bar, select columns A through M (if you want to apply the
formatting to the entire column) although more efficient would be to select
Column A through Column M down to the maximum number of rows you expect to
ever need. After you have made this selection... THEN you can follow the
directions in my original posting.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Why aren't you using Conditional Formatting instead... it is automatic,
that is, it doesn't require you to run a macro to get your cells colored.
Try this and see what you think...

Go to your worksheet and click Format/Condition Formatting in Excel's menu
bar. Click the Add button so that there are two Conditions showing. For
both of those conditions, click the first drop down field and select
"Formula Is". Now, for Condition 1, put this formula in the second
field...

=$K1="Validated"

Then click the Format button for Condition 1, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the Validated
rows in. Click OK to return to the Conditional Formatting dialog box.

Now, for Condition 2, put this formula in the second field..

=$K1="Needs to be Validated"

Then click on the Format button for Condition 2, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the "Needs to
be Validated" row in. Finally, OK your way back to the worksheet. Your
rows should now be highlighted in the colors you selected and, every time
you place a value of "Validated" or "Needs to be Validated" in Column K,
the row will automatically get highlighted.

Rick


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to set
the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have
now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy

I'll admit I have zero experience with databases linked to worksheets, so
I'd be interested in knowing... when you tried the Conditional Formatting,
it didn't work? I was under the impression that the Conditional Formatting
on a cell was independent of how the data got into the cell. It was my
understanding that if you imported the data into the worksheet, and if (in
your case) Column K had either "Validated" or "Needs to be Validated" in one
of its cells, then the row would automatically get highlighted in the colors
you chose. Are you saying that when you tried my suggestion, this didn't
happen?

Rick


"Judy Ward" wrote in message
...
Thank you very much for responding. I did try your suggestion to see how
it
worked. I didn't mention in my original post that this data lives in an
Access database and is being exported out to Excel. I call an Excel macro
to
format the data. Your idea would be great if the data were being updated
in
the Excel spreadsheet, but since it is being updated in the database it's
better for me to format the cells once through code.

Thanks again,
Judy

"Rick Rothstein (MVP - VB)" wrote:

I forgot to tell you... BEFORE you go to the Format/Conditional
Formatting
in Excel's menu bar, select columns A through M (if you want to apply the
formatting to the entire column) although more efficient would be to
select
Column A through Column M down to the maximum number of rows you expect
to
ever need. After you have made this selection... THEN you can follow the
directions in my original posting.

Rick


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Why aren't you using Conditional Formatting instead... it is automatic,
that is, it doesn't require you to run a macro to get your cells
colored.
Try this and see what you think...

Go to your worksheet and click Format/Condition Formatting in Excel's
menu
bar. Click the Add button so that there are two Conditions showing. For
both of those conditions, click the first drop down field and select
"Formula Is". Now, for Condition 1, put this formula in the second
field...

=$K1="Validated"

Then click the Format button for Condition 1, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the
Validated
rows in. Click OK to return to the Conditional Formatting dialog box.

Now, for Condition 2, put this formula in the second field..

=$K1="Needs to be Validated"

Then click on the Format button for Condition 2, click the Patterns tab
on
the dialog box and pick a color from the chart to highlight the "Needs
to
be Validated" row in. Finally, OK your way back to the worksheet. Your
rows should now be highlighted in the colors you selected and, every
time
you place a value of "Validated" or "Needs to be Validated" in Column
K,
the row will automatically get highlighted.

Rick


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to
set
the
fill color for the entire row. Ideally, the code would only set the
fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have
now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just
the
cell?

Thank you,
Judy

On Apr 19, 5:21*am, Judy Ward
wrote:
Thank you very much for responding. *I did try your suggestion to see how it
worked. *I didn't mention in my original post that this data lives in an
Access database and is being exported out to Excel. *I call an Excel macro to
format the data. *Your idea would be great if the data were being updated in
the Excel spreadsheet, but since it is being updated in the database it's
better for me to format the cells once through code.

Thanks again,
Judy

"Rick Rothstein (MVP - VB)" wrote:



I forgot to tell you... BEFORE you go to the Format/Conditional Formatting
in Excel's menu bar, select columns A through M (if you want to apply the
formatting to the entire column) although more efficient would be to select
Column A through Column M down to the maximum number of rows you expect to
ever need. After you have made this selection... THEN you can follow the
directions in my original posting.

Rick

"Rick Rothstein (MVP - VB)" wrote in
. ..
Why aren't you using Conditional Formatting instead... it is automatic,
that is, it doesn't require you to run a macro to get your cells colored.
Try this and see what you think...

Go to your worksheet and click Format/Condition Formatting in Excel's menu
bar. Click the Add button so that there are two Conditions showing. For
both of those conditions, click the first drop down field and select
"Formula Is". Now, for Condition 1, put this formula in the second
field...

=$K1="Validated"

Then click the Format button for Condition 1, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the Validated
rows in. Click OK to return to the Conditional Formatting dialog box.

Now, for Condition 2, put this formula in the second field..

=$K1="Needs to be Validated"

Then click on the Format button for Condition 2, click the Patterns tab on
the dialog box and pick a color from the chart to highlight the "Needs to
be Validated" row in. Finally, OK your way back to the worksheet. Your
rows should now be highlighted in the colors you selected and, every time
you place a value of "Validated" or "Needs to be Validated" in Column K,
the row will automatically get highlighted.

Rick

"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. *I want to set
the
fill color for the entire row. *Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have
now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
* *Set r = Range("K1", Range("K65536").End(xlUp))
* *For n = 2 To r.Rows.Count
* * * *If r.Cells(n, 1) = "" Then
* * * * * *' Do nothing
* * * *ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
* * * * * *r.Cells(n, 1).Select
* * * * * *With Selection.Interior
* * * * * * * *.ColorIndex = 37
* * * * * * * *.Pattern = xlSolid
* * * * * *End With
* * * *ElseIf r.Cells(n, 1) = "Validated" Then
* * * * * *r.Cells(n, 1).Select
* * * * * *With Selection.Interior
* * * * * * * *.ColorIndex = 35
* * * * * * * *.Pattern = xlSolid
* * * * * *End With
* * * *End If
* *Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy- Hide quoted text -

- Show quoted text -

Hi Judy,

In that case please change the two lines that change the interior
color like following:
Original line in code: With Selection.Interior
Change it to: With Selection.EntireRow.Interior

Thanks
Anant

Sub Color()
Selection.EntireRow.Interior.ColorIndex = 37

End Sub


"Judy Ward" wrote:

I have code that loops through all rows, checks the value in a specific cell
and based on that value sets the fill color for that cell. I want to set the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill color
for the entire row (beyond column N) would be better than what I have now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the cell?

Thank you,
Judy

Judy,

Depending on the size of your range, it might make sense to try conditional
formatting.

In using the sub, you shouldn't have to select the cells as you go. In the
short example below, I just have the routine scan column B, look for the
value 2 and color the cells of that row from column A to column M light blue
if a 2 is found.
______________________________
Sub ColorPartOfRow()
Dim r As Integer
r = Sheets(1).UsedRange.Rows.Count
For x = 1 To r
If Cells(x, 2).Value = 2 Then
For y = 1 To 13
Cells(x, y).Interior.ColorIndex = 37
Next y
End If
Next x
End Sub
__________________________________

If you had wanted to color the entire row, it could be a tad shorter with
__________________________________
Sub ColorRows()
Dim r As Integer
r = Sheets(1).UsedRange.Rows.Count
For x = 1 To r
If Cells(x, 2).Value = 2 Then
Cells(x, 2).EntireRow.Interior.ColorIndex = 37
End If
Next x
End Sub
_____________________________________

Steve Yandl


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to set
the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy

Thank you very much for responding. Your suggestion is exactly what I needed.

Thanks again!
Judy

"Steve Yandl" wrote:

Judy,

Depending on the size of your range, it might make sense to try conditional
formatting.

In using the sub, you shouldn't have to select the cells as you go. In the
short example below, I just have the routine scan column B, look for the
value 2 and color the cells of that row from column A to column M light blue
if a 2 is found.
______________________________
Sub ColorPartOfRow()
Dim r As Integer
r = Sheets(1).UsedRange.Rows.Count
For x = 1 To r
If Cells(x, 2).Value = 2 Then
For y = 1 To 13
Cells(x, y).Interior.ColorIndex = 37
Next y
End If
Next x
End Sub
__________________________________

If you had wanted to color the entire row, it could be a tad shorter with
__________________________________
Sub ColorRows()
Dim r As Integer
r = Sheets(1).UsedRange.Rows.Count
For x = 1 To r
If Cells(x, 2).Value = 2 Then
Cells(x, 2).EntireRow.Interior.ColorIndex = 37
End If
Next x
End Sub
_____________________________________

Steve Yandl


"Judy Ward" wrote in message
...
I have code that loops through all rows, checks the value in a specific
cell
and based on that value sets the fill color for that cell. I want to set
the
fill color for the entire row. Ideally, the code would only set the fill
color for the columns I am using (columns A - M), but setting the fill
color
for the entire row (beyond column N) would be better than what I have now.

Dim r As Range
Dim n As Integer
' Format_Status_Colors
Set r = Range("K1", Range("K65536").End(xlUp))
For n = 2 To r.Rows.Count
If r.Cells(n, 1) = "" Then
' Do nothing
ElseIf r.Cells(n, 1) = "Needs to be Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf r.Cells(n, 1) = "Validated" Then
r.Cells(n, 1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Next n

Can anyone help me set the fill color for the row I am on, not just the
cell?

Thank you,
Judy