VBA: Entire Row Color

[Weezer]

Hello,

I was curious to know if there's a way to make my column headers black without formatting it before hand & by doing it with the help of VBA. I want the entire row "2" to be black. But I want this formatting only to carry on as long as there is data on that row, if there isn't data, I don't want the formatting to carry on. Can someone point me in the direction for conditional formatting for this problem.

Thanks.

[grichey]

did you try the recorder?

[grichey]

In the spirit of giving:

Sub OnceYouGoBlack()

Dim pecker As Range


Set pecker = Range("2:2")

If WorksheetFunction.CountA(pecker) = 0 Then
Rows("2:2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End If


End Sub

[Simon Lloyd]

Or maybe in the worksheet event codePrivate Sub Worksheet_Activate()
Dim cRng As String, rRng As String
rRng = Me.Cells(2, 2).Address
cRng = Cells(2, Me.UsedRange.Columns.Count).Address
Me.Range(rRng & ":" & cRng).Interior.ColorIndex = 1
End Sub


Private Sub Worksheet_Deactivate()
Dim cRng As String, rRng As String
rRng = Me.Cells(2, 2).Address
cRng = Cells(2, Me.UsedRange.Columns.Count).Address
Me.Range(rRng & ":" & cRng).Interior.ColorIndex = xlNone
End Sub

[Weezer]

Hey guys.

The pecker code doesn't work, it gives me a compile error & the debug points at the ".ThemeColor = xlThemeColorLight1"

Simon Lloyd's worksheet event code works but I get a white color instead of Black. And it starts from B2 instead of A2, any ideas why it might be doing this?

Thanks for all your help guys.

[grichey]

That's probably a version issue. I'm on excel 2007. Switch it to a excel 03 color and it should work fine. If you want, just open the macro recorder, start record macro, highlight whole row and color it black; stop the recorder; open editor and just copy whatever the vba is over and delete that portion of my pecker macro.

[Simon Lloyd]

Mine works from B2 as it is set from there, in >xl2003 you need to find your own colorindex for black ;)
Here's the revised code:

Private Sub Worksheet_Activate()
Dim cRng As String, rRng As String
rRng = Me.Cells(2, 1).Address
cRng = Cells(2, Me.UsedRange.Columns.Count).Address
Me.Range(rRng & ":" & cRng).Interior.ColorIndex = 1
End Sub


Private Sub Worksheet_Deactivate()
Dim cRng As String, rRng As String
rRng = Me.Cells(2, 1).Address
cRng = Cells(2, Me.UsedRange.Columns.Count).Address
Me.Range(rRng & ":" & cRng).Interior.ColorIndex = xlNone
End Sub