VBA: Issue with adding CF Rules using Wildcards & Named List (Range)

[zero269]

Hello,

I'm having some trouble with figuring out how to add CF Rules via VBA that use both wildcards and a named range (list).

I've been playing around with ways to re-add my Conditional Formatting Rules to serve as both a CFR backup & restore solution... as well as learning VBA.
So far, I've got BLANK, ZERO and Duplicate Values working great. However, now I've moved onto more complex Rules that got me stumped.

Here's one CF Rule that I'm trying to add with VBA in my VBA Testing Workbook. It works when added manually via the Conditional Formatting Rules Manager (CFRM).

=SUM(COUNTIF(F3,"*"&lstList&"*"))

What it does: Highlights values in column F that contain like values from a named range (list) using wildcards.

My dilema is that I can't get that formula to get added via VBA. I've noticed that it keeps spacing "*" to " * " causing VBA error: Run-time error '13': Type mismatch which then flags the following line in my VBA code: .FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(COUNTIF(F3," * "&lstList&" * "))"
Removing the spaces manually in the CFRM will remediate this issue after it's been added. Unfortunately, after many different experiments, I can't even get that far again.
Here's what I'm working with:

Sub CFR_Add_NamedList()

    Dim Series As Range, lstList As Range
    Set Series = ActiveSheet.Range("tblScores[Series]")     'Table range to apply CF Rule to
'    Set lstList = ActiveSheet.Range("N2:N3")    'List of values to use for CF Rule
    
    With Series
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(COUNTIF(F3," * "&lstList&" * "))" 'VBA is spacing " * " automatically
'        .FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(COUNTIF(F3,lstList))" 'this line works without wildcards
        .FormatConditions(.FormatConditions.Count).Interior.Color = 14348258 'Green 6 80%
        .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With

End Sub

I tried incorporating one example where they used a variable for the formula, but that wouldn't take either:

Dim formula As String
formula = "=SUM(COUNTIF(F3,"*"&lstList&"*"))"
.FormatConditions.Add Type:=xlExpression, Formula1:=formula

Does anyone know how a formula can be added without those wildcard characters being spaced by the VB Editor?

Thank you,

 

[zero269]

OK, so disregard my request.
Apparently, I just needed to use double quotes in place of single quotes.

.FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(COUNTIF(F3,""*""&lstList&""*""))"

This is what happens when you're a VBA noob!