Conditional Formatting Rules Order Not What Expected

[ColoKevMan]

The below code works fine, except that when it gets to the With Range("D14:E" & [lRowEnd4] - 3).FormatConditions.Add Type:=xlExpression, Formula1:="=$AV14=""""" line, it places that line at the top of the conditional rules - I don't want that. I want it in the order the code places it. What do I do?

Sub FCsumBSA()
'
'
Dim lRowEnd4 As Long, lRowEnd As Long, EndCell As Long
Dim rng As Range

lRowEnd = Range("D60000").End(xlUp).Row
lRowEnd4 = Range("A60000").End(xlUp).Row
EndCell = Cells(Rows.Count, "AW").End(xlUp).Row - 3
Set rng = Range("$BF$1:XFD" & [lRowEnd])
'
Cells.FormatConditions.Delete
'Format column AV and other ranges
    With Range("AV13:AV" & [lRowEnd4]).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
'Apply conditional formating
     With Range("$D$10")
       .FormatConditions.Add Type:=xlExpression, Formula1:="=$W$13>0"
        With .FormatConditions(.FormatConditions.Count).Font
            .Bold = True
            .Italic = False
            .Color = RGB(38, 116, 38)
            .TintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
                 
       .FormatConditions.Add Type:=xlExpression, Formula1:="=$W$13<=-1"
         With .FormatConditions(.FormatConditions.Count).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
           
         With .FormatConditions(.FormatConditions.Count).Interior
             .Color = 255
             .TintAndShade = 0
         End With
             .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
             
     With Range("$G$10:$R$11")
       .FormatConditions.Add Type:=xlExpression, Formula1:="=G$10=""Budget"""
         With .FormatConditions(.FormatConditions.Count).Interior
            .Color = 5296274
            .TintAndShade = 0
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
      
     With Range("$G$10:$R$11", "$AA$10:$AL$11")
       .FormatConditions.Add Type:=xlExpression, Formula1:="=G$10=""Forecast"""
         With .FormatConditions(.FormatConditions.Count)
            .Interior.Color = vbYellow
            .Font.Color = vbBlack
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
      
     With Range("$G$12:$R$12")
       .FormatConditions.Add Type:=xlExpression, Formula1:="=G$8<>G$13"
         With .FormatConditions(.FormatConditions.Count).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
         End With
          .FormatConditions(.FormatConditions.Count).StopIfTrue = False
          
         With .FormatConditions(.FormatConditions.Count).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = -0.249946592608417
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
      
      With Range("$AA$12:$AL$12")
       .FormatConditions.Add Type:=xlExpression, Formula1:="=AA$8<>AA$13"
         With .FormatConditions(.FormatConditions.Count).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
         End With
          .FormatConditions(.FormatConditions.Count).StopIfTrue = False
          
         With .FormatConditions(.FormatConditions.Count).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = -0.249946592608417
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
      
    With Range("D14:E" & [lRowEnd4] - 3)
       .FormatConditions.Add Type:=xlExpression, Formula1:="=$AV14="""""
       With .FormatConditions(.FormatConditions.Count).Interior
        .Color = vbWhite
       End With
        .FormatConditions(.FormatConditions.Count).StopIfTrue = False
'
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$AV14=0"
       With .FormatConditions(.FormatConditions.Count).Interior
       .PatternColorIndex = xlAutomatic
       .ThemeColor = xlThemeColorAccent6
       .TintAndShade = 0.599963377788629
      End With
       .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With
'
' Apply conditional formatting for Total lines
    With Range("B14:AZ" & [lRowEnd4] - 3)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT($B14,5)=""Total"""
    .FormatConditions(1).SetFirstPriority
       With .FormatConditions(.FormatConditions.Count).Interior
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -0.1
       End With
    End With
    
' Apply conditional formatting = font is RED if NOT GXL formula and BLACK if SubTotal line
    With Range("G14:R" & [lRowEnd4] - 3)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=LEFT(FT(G14),4)<>""=GXL"""
       With .FormatConditions(.FormatConditions.Count).Font
        .Color = -16776961
       End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=LEFT(FT(G14),9)=""=SUBTOTAL"""
       With .FormatConditions(.FormatConditions.Count).Font
         .Color = 0
        End With
          .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    
'Apply conditional formatting for Actuals and Forecast
    .FormatConditions.Add Type:=xlExpression, Formula1:="=G$10=""Actuals"""
       With .FormatConditions(.FormatConditions.Count)
        .Font.Color = -16777024
        .Interior.Color = vbWhite
       End With
         .FormatConditions(.FormatConditions.Count).StopIfTrue = False
'
    .FormatConditions.Add Type:=xlExpression, Formula1:="=G$10=""Forecast"""
       With .FormatConditions(.FormatConditions.Count)
        .Interior.Color = vbYellow
        .Font.Color = vbBlack
       End With
         .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With
    
      With Range("$AW$8:$AX$8")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=FALSE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .Pattern = xlGray50
            .PatternColorIndex = xlAutomatic
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With
      With Range("$AW$9:$AX9")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=TRUE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .Pattern = xlGray50
            .PatternColorIndex = xlAutomatic
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With
      With Range("$AW$10:$AX$11")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=FALSE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .TintAndShade = 0.799981688894314
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
           
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=TRUE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .Color = RGB(0, 176, 240)
            .TintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
      
      With Range("$AY$10:$AZ$11")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=TRUE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .TintAndShade = 0.799981688894314
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
           
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$BD$5=FALSE"
        With .FormatConditions(.FormatConditions.Count).Interior
            .Color = RGB(204, 192, 216)
            .TintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
      End With
    With Range("AW14:AW" & EndCell & ", AY14:AY" & EndCell)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(FT(B14),5)<>""Total"""
        With .FormatConditions(.FormatConditions.Count).Interior
            .Pattern = xlGray50
            .PatternColorIndex = xlAutomatic
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
    End With
    
     With Range("$AZ$8")
        .FormatConditions.Add Type:=xlTextString, String:="Check only 1 box!", TextOperator:=xlContains
        With .FormatConditions(.FormatConditions.Count).Font
             .Bold = True
             .Color = vbWhite
             .TintAndShade = 0
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
           
        With .FormatConditions(.FormatConditions.Count).Interior
             .PatternColorIndex = xlAutomatic
             .Color = 255
             .TintAndShade = 0
         End With
           .FormatConditions(.FormatConditions.Count).StopIfTrue = False
     End With
'
' Insert blank row
    Rows(lRowEnd + 2).EntireRow.Insert
'
' Clear contents, formats and delete cells where SS Expand Rows places cell comments
    rng.ClearFormats
    rng.ClearContents
    rng.Delete Shift:=xlToLeft
'
' Select cell D5
    Cells(5, 4).Select
End Sub