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