VBA Conditional Format List Object Table

[Amapola]

Good Afternoon

I'm trying to get VBA to format a report I have to do regularly. I want to format the entire row depending on value in one of the columns. As the view can change and that might impact the columns the report downloads, I'm formatting the data as a table and want to use the table header to determine which column to use as a condition. I also want to delete a couple of columns after that.
Finally, I want to save the file as xlsx with the calculated filename.

I get an error 438 on this line, Object doesn't support this property
tbl.DataBodyRange.Rows.FormatConditions.Interior.Color = RGB(205, 5, 5)

I have tried various ways to mimic conditional formatting in VBA but none work. Note, it does not need to be dynamic.

Anybody see where I go wrong?

Thanks, Christine

Sub CalDueReport()


Dim shTable As Worksheet
Set shTable = Sheets("Table")
    
    Application.CutCopyMode = False
    
'Convert to Table
Dim tbl As ListObject
Dim src As Range
Set src = Range("A1").CurrentRegion
    ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=src, xlListObjectHasHeaders:=xlYes).Name = _
        "CalDue"
    Range("CalDue[#All]").Select
    ActiveSheet.ListObjects("CalDue").TableStyle = "TableStyleMedium1"
Set tbl = shTable.ListObjects("CalDue")

Dim sortcolumn As Range
Set sortcolumn = Range("CalDue[Calibration due]")
With tbl.Sort
   .SortFields.Clear
   .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
   .Header = xlYes
   .Apply
End With

'today's date for condition and file name
Dim CurrDate As Date
CurrDate = Date
Dim ReportDate As String
ReportDate = Format(Date, "yyyy-mm-dd")

'Delete any existing Conditional Formatting
tbl.DataBodyRange.FormatConditions.Delete


'Overdue
tbl.DataBodyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=CurrDate > INDIRECT(""CalDue[@Calibration due]"")"
tbl.DataBodyRange.Rows.FormatConditions.Interior.Color = RGB(205, 5, 5)

'Next Month
tbl.DataBodyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=CurrDate + 30 > INDIRECT(""CalDue[@Calibration due]"")"
tbl.DataBodyRange.Rows.FormatConditions.Interior.Color = RGB(255, 155, 50)

'Next 3 months
tbl.DataBodyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=CurrDate + 90 > INDIRECT(""CalDue[@Calibration due]"")"
tbl.DataBodyRange.Rows.FormatConditions.Interior.Color = RGB(250, 250, 5)

'Next 6 monts
tbl.DataBodyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=CurrDate +180 > INDIRECT(""CalDue[@Calibration due]"")"
tbl.DataBodyRange.Rows.FormatConditions.Interior.Color = RGB(50, 200, 200)



'Delete Column Tool type
tbl.ListColumns("Category").Delete


'Delete Last Table Row
tbl.TotalsRowRange.Delete

'Hide Items not calibrated
Dim iCol As Long
iCol = tbl.ListColumns("Status").Index
    tbl.ListObjects("CalDue").AutoFilter Field:=iCol, _
    Criteria1:=Array("Ready to Deploy", "Ready to Deploy Deployed", "Calibration Overdue - Do not use", "Calibration Overdue - Do not use Deployed")
          

'Save as report with month date
Dim ReportName As String
ReportName = "CalDueReport_" & ReportDate & ".xlsx"
ActiveWorkbook.SaveAs Filename:="C:\UserData\XXXX\XXXX\XXXX\" & ReportName, FileFormat:=51

    
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Amapola]

@bobsan42 - entirely possible. I write code in the Trial and Error Method. (mostly error usually! )