VBA is formatting the wrong "FormatCondition"...

[eboomer]

First, let me explain what I'm trying to do:
I have a spreadsheet with 7 different conditional formats. As it is basically a template, I'm trying to make this spreadsheet as easy as possible for somebody who maybe isn't great with excel to move cells around, and end up with the right conditional formatting. I don't think it should be too difficult to write a macro to clear the conditional formatting and re-apply it, so that's what I'm trying to do.

The problem I'm having:
The first few conditional formats the code applies are all just formatting the background color and not the font. Then when it adds conditional formats that affect the font ('Green formatting for status label for passing test sheet), it suddenly cannot seem to modify them, but instead it applies the font to the previous formats that only formatted the background color. Even when I change Selection.FormatConditions(1) to Selection.FormatConditions(2) or Selection.FormatConditions(3) (there are only 3 formats at that ponit), regardless of whether I specify 1 or 3 it still just applies the formatting to the same (wrong) format. With Selection.FormatConditions(2) it applies it to the other wrong format. I have also tried applying text formatting to the formats that currently only specify the background color, with the same results. Also, in the "'Input cell Failed formatting" block, at the ".Color = RGB(255, 255, 255)" line, I get a "Run-time error '9': Subscript out of range" error. How could it be out of range if I'm modifying the first FormatCondition?

Details/Explainations regarding the code:
The code below is based off of recording a macro from the actual conditional formatting that was already in the spreadsheet. "Status" is a merged cell, the named range explicitly refers to "$D$2:$F$4". "InputCells" is actually multiple dis-continuous areas. There are no cells shared between "Status" and "InputCells". The first conditional format of InputCells, which only formats the background, is executed correctly, the subsequent format of both background color and font color results in a run-time error. "SerialNo" is one of the ranges in "InputCells".

Any help as to why this doesn't work, or suggestions on how to get it to work, would be appreciated.

Public Sub TestSheetCondFormat()
    Dim myActiveCell As Range
    Dim CompletionCell As Range, CompletionAddress As String
    Dim FailCell As Range, FailAddress As String
    
    Dim InitSelection As Range, IntitActive As Range
    
    Set InitSelection = Selection
    Set InitActive = ActiveCell
    
'    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    
    Cells.FormatConditions.Delete
    
    Set CompletionCell = Cells(Range("SerialNo").Cells(1, 1).Row, Range("myHiddenColumns").Cells(1, 1).Column)
    CompletionAddress = CompletionCell.Address(RowAbsolute:=False)
    
    Set FailCell = Cells(Range("SerialNo").Cells(1, 1).Row, Range("myHiddenColumns").Cells(1, 2).Column)
    FailAddress = CompletionCell.Address(RowAbsolute:=False)
    
  'Sheet Incomplete formatting
    Cells.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SheetComplete=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.799981688894314
        End With
        .StopIfTrue = False
    End With
    
  'Good input cell formatting
    Range("InputCells").Select
    Range("SerialNo").Cells(1, 1).Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & CompletionAddress & "<>"""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13500365
            .TintAndShade = 0
        End With
        .StopIfTrue = False
    End With
    
  'Sheet Failed Formatting
    Cells.Select
    Range("SerialNo").Cells(1, 1).Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SheetFail=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599963377788629
        End With
        .StopIfTrue = False
    End With
    
  'Empty input cell formatting
    Range("InputCells").Select
    Range("SerialNo").Cells(1, 1).Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(" & CompletionAddress & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
        End With
        .StopIfTrue = False
    End With
    
  'Green formatting for status label for passing test sheet
    Range("Status").Select
    Selection.Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(SheetFail=0,SheetComplete=1)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Font
            .Color = -13715410
            .TintAndShade = 0
        End With
        .StopIfTrue = False
    End With
    
  'Input cell Failed formatting
    Range("InputCells").Select
    Range("SerialNo").Cells(1, 1).Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & FailAddress
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Font
            .Color = RGB(255, 255, 255)
            .TintAndShade = 0
        End With
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .StopIfTrue = False
    End With
    
  'Blue formatting for status label for incomplete sheet
    Range("Status").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(SheetComplete=0,SheetFail=0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        With .Font
            .ThemeColor = xlThemeColorAccent5
            .TintAndShade = -0.249946592608417
        End With
        .StopIfTrue = False
    End With

    InitSelection.Select
    InitActive.Activate

    ActiveSheet.Protect
'    Application.ScreenUpdating = True
    
End Sub

[eboomer]

Bueller?
...
Bueller?
...
Bueller?
...
Bueller?
...

...hmmmmm....

Example spreadsheet attached, if it helps...

Thanks