Want to select row

[Judy Ward]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Office_Novice]

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

End Sub

 

[Steve Yandl]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Judy Ward]

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

Thanks again!
Judy

 

[Judy Ward]

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\)]

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

 

[Anant.Basant]

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

:




- 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