For cells in a column: how can I change Interior.Color of EntireRow based the font.color?

[dougmarkham]

Hi Folks,

I'm trying to format my dynamic excel table, so that IF text in a cell in column A is colored white, then the entire row--within the table boundary only---will have Interior.ColorIndex = 25.

The only code I've managed to use to successfully change a row within a table to a different interior color (using tablename[header-name] reference type is:

    Dim LastRow As Long
    Dim ColorFlag As Boolean
    Dim i As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ColorFlag = False
    For i = 1 To LastRow
            If (Cells(i, "D").Value <> Cells(i + 1, "D").Value) Then ColorFlag = Not (ColorFlag)
                If (ColorFlag) Then
                    Range("TotSumTable[Alias/CC/Country]:TotSumTable[Price(£)/Box]").Rows(i).Interior.ColorIndex = 19
                Else
                    Range("TotSumTable[Alias/CC/Country]:TotSumTable[Price(£)/Box]").Rows(i).Interior.ColorIndex = xlNone
                End If
    Next i

However, if I try and modify

 If (Cells(i, "D").Value <> Cells(i + 1, "D").Value) Then ColorFlag = Not (ColorFlag)

, to for instance

If (Cells(i, "A").Font.ColorIndex <> 1) Then ColorFlag = Not (ColorFlag)

, then this doesn't work and fails to color the correct rows.

I don't understand the syntax

If (Cells(i, "D").Value <> Cells(i + 1, "D").Value) Then ColorFlag = Not (ColorFlag)

, and I can't find useful hits on Google chrome after multiple searches with different wording.

Would one of you be able to explain this syntax and help me achieve enough understanding to achieve my task?

Kind regards,

Doug.

 

[Fluff]

How about

Sub dougmarkham()
   Dim i As Long
   With ActiveSheet.ListObjects("TotSumTable")
      For i = 1 To .DataBodyRange.Rows.Count
         If .DataBodyRange.Cells(i, 1).DisplayFormat.Font.ColorIndex = 2 Then
            .ListRows(i).Range.Interior.ColorIndex = 25
         End If
      Next i
   End With
End Sub

 

[dougmarkham]

How about

Sub dougmarkham()
   Dim i As Long
   With ActiveSheet.ListObjects("TotSumTable")
      For i = 1 To .DataBodyRange.Rows.Count
         If .DataBodyRange.Cells(i, 1).DisplayFormat.Font.ColorIndex = 2 Then
            .ListRows(i).Range.Interior.ColorIndex = 25
         End If
      Next i
   End With
End Sub

Hi Fluff,

That works!

Regarding the syntax, I take it if I wanted to do this to e.g., Bold cells, then I would need to use: DisplayFormat.Font.Bold = True Then...?
Wonder if it works for all formatting!?

Your code syntax (.DataBodyRange.Rows.Count and use of DisplayFormat.) is completely new to me, and is definitely going to be useful again, so big thanks!

Kind regards,

Doug

 

[Fluff]

I wanted to do this to e.g., Bold cells, then I would need to use: DisplayFormat.Font.Bold = True Then...?

That's right.
The DisplayFormat may not be needed, but it will pick up the cell format regardless of if it's applied manually or by conditional formatting.

 

[dougmarkham]

That's right.
The DisplayFormat may not be needed, but it will pick up the cell format regardless of if it's applied manually or by conditional formatting.

Thanks Fluff!

Just one more thing ... Let's say I wanted to make table rows a different interior color; however, instead of using the attribute of font color, I wanted to do this interior color change to cells starting with S (e.g., Left(cell, 1) = S). Is that possible by adapting your code? (I gave it a go)

Kind regards,

Doug.

P.S. the reason I asked is: I used the following code to make font bold and change the font color; however, of course, I found changing the interior color of entire rows didn't look good. It be interesting if your code can take

For Each cell In Selection
    If Left(cell, 1) <> "S" Then
    GoTo AA
    Else
    CellRow = cell.Row
    'MsgBox CellRow
    cell.EntireRow.Select
    With Selection
        .Font.Bold = True
        .Font.ColorIndex = 2
    End With
AA:
    End If
Next cell

 

[Fluff]

You can do that like

Sub dougmarkham()
   Dim i As Long
   With ActiveSheet.ListObjects("TotSumTable")
      For i = 1 To .DataBodyRange.Rows.Count
         If Left(.DataBodyRange.Cells(i, 1), 1) = "S" Then
            .ListRows(i).Range.Interior.ColorIndex = 25
         End If
      Next i
   End With
End Sub

 

[dougmarkham]

You can do that like

Sub dougmarkham()
   Dim i As Long
   With ActiveSheet.ListObjects("TotSumTable")
      For i = 1 To .DataBodyRange.Rows.Count
         If Left(.DataBodyRange.Cells(i, 1), 1) = "S" Then
            .ListRows(i).Range.Interior.ColorIndex = 25
         End If
      Next i
   End With
End Sub

Hi Fluff,

Ah, so the .DatabodyRange. is integral to the reference.
Thanks for this example I think I get it now.

Playing around loosely based on your code; weirdly, this seems to work too...

For i = 1 To Range(Cells(2, 1), Cells(LRow, 1)).Rows.Count
    If Left(Cells(i, 1), 1) = "S" Then
        Range("TotSumROITable").Rows(i - 1).Interior.ColorIndex = 25
    End If
Next i

I wonder why the line

Range("TotSumROITable").Rows(i).Interior.ColorIndex = 25

seems to highlight all the rows beneath the target row? Lol, strange.

Thanks Fluff!

Kind regards,

Doug.

 

[Fluff]

You're welcome & thanks for the feedback.