CODEWITHSUNDEEP
vbaexcelconditional-formatting
Daniel McGeever
Daniel McGeever

Reputation: 51

Conditional Formatting, although applied no format comes up

First off this is one of the best forums ive been on. I've only recently created an account but I have already learned so much from this forum.

My question as per the title is with regards to Conditional Formatting. As seen below in the code I have so far I have applied a background color as the formatting. But after running this macro, although the conditions are applied to the selected cells there is no formatting to with it. When I click on manage existing rules, the conditions are there but it comes up with "No Format Set". As can be seen here: Screenshot

Sub tableSetup()

    Dim tbl As ListObject

    Set tbl = ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Selection, xllistobjecthasheaders:=xlYes, tablestylename:="Custom")

    cellStr = tbl.DataBodyRange.Cells(1).Address
    cellStr = Replace(cellStr, "$", "")
    formulaStr = "=IsFormula(" & cellStr & ")"

    With tbl.DataBodyRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:=formulaStr
        .FormatConditions.Interior.Color = RGB(191, 191, 191)
    End With

End Sub

Upvotes: 3

Views: 768

Answers (1)

user4039065
user4039065

Reputation:

You aren't telling it which FormatConditions to apply the formatting to.

With tbl.DataBodyRange
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:=formulaStr
    .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(191, 191, 191)
End With

You've deleted all other .FormatConditions so legitimately, this could be referred to as .FormatConditions(1) but when you add a .FormatConditions, it is always the last one in the queue until you do something like .FormatConditions(.FormatConditions.Count).SetFirstPriority which moves it to the front of the queue.

You can also use the object created with .FormatConditions.Add to form a nested With ... End With block for correctly referencing multiple operations.

With tbl.DataBodyRange
    .FormatConditions.Delete
    with .FormatConditions.Add(Type:=xlExpression, Formula1:=formulaStr)
        .Interior.Color = RGB(191, 191, 191)
        .SetFirstPriority
    end with
End With

Upvotes: 2

Related Questions