Detect background color of the tables with only "quick stylies" applied

71GA 0
Viewed 25

I have tables Table32 and Table28.

Both were first created and then each of them was selected and then a quick style was selected for each of them from the Excel ribbon's link "Table design / Quick styles".

Table32 was assigned a "quick style" by the name White, Table Style Medium 4 and Table28 was assigned a "quick style" by the name Green, Table Style Medium 7.

Now they look like this:

enter image description here

Now I want to write a VBA code which does something regarding of the table's color.

So I found out that I can detect TableStyle like this:

Debug.Print Sheet20.ListObjects("Table32").TableStyle
TableStyleMedium4
Debug.Print Sheet20.ListObjects("Table28").TableStyle
TableStyleMedium7

But I simply can't detect the color! I get the same value for both tables!?

Debug.Print Sheet20.ListObjects("Table32").HeaderRowRange.Interior.Color
 16777215 
Debug.Print Sheet20.ListObjects("Table28").HeaderRowRange.Interior.Color
 16777215

I was expecting to get White and Green according to the chosen "quick styles" White, Table Style Medium 4 and Green, Table Style Medium 7 that I selected from the ribbon...

vba excel
1 Answers

You have to access the Tablestyle-object to retrieve the color.

Sub testListobjectTableStyle()

Dim arrLo(1) As ListObject
Set arrLo(0) = ActiveSheet.ListObjects(1)
Set arrLo(1) = ActiveSheet.ListObjects(2)

Dim i As Long, ts As TableStyle
For i = 0 To 1
    Debug.Print arrLo(i).TableStyle

    Set ts = ThisWorkbook.TableStyles(arrLo(i).TableStyle)
    Debug.Print "style color:", ts.TableStyleElements(xlHeaderRow).Interior.Color
    Debug.Print "applied color:", arrLo(i).HeaderRowRange.Interior.Color
Next

End Sub

HeaderRowRange.Interior.Color will return vbWhite=16777215, if nothing was changed or will return the color the user applied manually to the individual header.

So you could use this function to return the "visible" color:

Public Function getHeaderRowRangeInteriorColor(lo As ListObject) As Long
With lo
    If .HeaderRowRange.Interior.Color <> vbWhite Then
        getHeaderRowRangeInteriorColor = .HeaderRowRange.Interior.Color
    Else
        Dim ts As TableStyle
        Set ts = ThisWorkbook.TableStyles(.TableStyle)
        getHeaderRowRangeInteriorColor = ts.TableStyleElements(xlHeaderRow).Interior.Color
    End If
End With
End Function
useryPy710
Related