Build a loop into a conditional formatting macro

[FryGirl]

I've been able to build one loop into this macro to switch interior.colorindex, but I would also like to loop the formula part.

I tried

Formula1:=Fm & i

but that does not work

Sub ApplyCF()
    Dim wsSrc   As Worksheet: Set wsSrc = Sheet5
    Dim LastCol As Long: LastCol = wsSrc.Cells(5, Columns.Count).End(xlToLeft).Column
    Dim LastRow As Long: LastRow = wsSrc.Range("C" & Rows.Count).End(xlUp).Row
    Dim myNum   As Long
    Dim myColor As Variant
    Const Fm1   As String = "=AND(N($D6),I$5>=$D6,I$5<=$H6)"
    Const Fm2   As String = "=WEEKDAY(I$4,2)>5"
    Const Fm3   As String = "=MATCH(I$4,holidays,0)"
    Const Fm4   As String = "=ISODD($A6)"
    Const Fm5   As String = "=ISEVEN($A6)"
    Dim i       As Long: i = 1
    
    Application.ScreenUpdating = False
    With wsSrc.Cells
        With .FormatConditions
            .Delete
        End With
    End With

    wsSrc.Range("I6").Select
    
    myColor = Array(32, 20)
    
    For myNum = LBound(myColor) To UBound(myColor)
    
        With wsSrc.Range(Cells(6, 9), Cells(LastRow, LastCol))
            
            With .FormatConditions
                
                .Add(Type:=xlExpression, Formula1:=Fm & i).Interior.ColorIndex = myColor(myNum)
                
            End With
        
        End With
        
        i = i + 1
        
    Next myNum
    
    Application.ScreenUpdating = True
    
End Sub

 

[GWteB]

Names of variables and constants are fixed after compile time. At run-time, those names cannot be accessed by, for example, merging texts in a variable manner.

That said, you're using two colors so your loop will run twice. How does that compare to the five formulas you want to use? The final desired CF determines how the code will look like, so explain us what you want regarding the CF.

 

[FryGirl]

Thank you GWteB for your help,

After giving this some thought, there probably wasn't a need for what I was trying to do, so, I'm just going with this.

With wsSrc.Range(Cells(6, 9), Cells(LastRow, LastCol))
    .FormatConditions.Add(Type:=xlExpression, Formula1:=Fm2).Interior.ColorIndex = 16
    .FormatConditions.Add(Type:=xlExpression, Formula1:=Fm3).Interior.ColorIndex = 22
    .FormatConditions.Add(Type:=xlExpression, Formula1:=Fm1).Interior.ColorIndex = 41
    .FormatConditions.Add(Type:=xlExpression, Formula1:=Fm4).Interior.ColorIndex = 34
    .FormatConditions.Add(Type:=xlExpression, Formula1:=Fm5).Interior.ColorIndex = 37
End With

 

[GWteB]

I think I would have advised that in the end. Well done and thanks for letting me know.