Search code examples
excelvbalistobject

VBA DataBodyRange background color error 91?

I am trying to add a color to my header and my databodyrange but the header is being coloured but the body isn't:

Tbl.HeaderRowRange.Interior.Color = RGB(62, 199,98)
Tbl.DataBodyRange.Interior.Color = RGB(151, 225, 137)

code:

Set Tbl = .ListObjects.Add(xlSrcRange, Source:=.Range("F" & NextRow + 8 & ":I" & NextRow + 8), XlListObjectHasHeaders:=xlYes)
        
                Tbl.Name = "KozijnAspect_" & I
                
                Tbl.HeaderRowRange(1).Value = "Kozijn"
                Tbl.HeaderRowRange(2).Value = "Gevel Type"
                Tbl.HeaderRowRange(3).Value = "Orientatie"
                Tbl.HeaderRowRange(4).Value = "Stand"
                
                Tbl.HeaderRowRange.Interior.Color = RGB(62, 199, 98)
                Tbl.DataBodyRange.Interior.Color = RGB(62, 199, 98)

Error code: Object variable not set (Error 91)

  • I copied the HeaderRowRange, pasted it below and changed RGB values and switched it to DataBodyRange

  • When I debug it just goes straight to the next line, it's not going to another module / excel object or anything.

Solution

  • Here it is a piece of code from a bigger pie...

    I am afraid that your code does not produce any DataBodyRange and that's why VBA is not able to do something on a non existing object...

    When your code is stopped on this yellow row, please go on Excel Table and manually insert one, or two rows. Then, go back in IDE and press F5...

    You may have a good surprise... :)