vba excel conditional formatting on listObject

Question

I need to strikeout a row cells depending of a relative column value in a listobject. How i can add a conditional formatting on all row cells based on relative column value in vba? Ex: If the cell value = 1, then the entire row need to be strikeout

I tried this:

Dim qt As ListObject
qt = Sheets.ListObjects(1)
With qt.DataBodyRange
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=[MyColumn]=0"
.FormatConditions(0).Font.StrikeOut = True
End With

Unknown Unknown · Accepted Answer · 2016-03-16T10:58:14

The following uses the .DataBodyRange property to retrieve the address in the top-left corner to be used in a formuLa.

With Worksheets("Sheet1")
    With .ListObjects("Table1")
        With .DataBodyRange
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, _
                Formula1:="=" & .Cells(1, 1).Address(0, 1) & "=1"
            .FormatConditions(1).Font.Strikethrough = True
        End With
    End With
End With

Note that the FormatConditions object has a 1-based ineex and the use of Font.Strikethrough property, not Font.StrikeOut.

vba excel conditional formatting on listObject

1 Answers