Setting formatconditions doesn't work with certain ranges?

Question

I have the following code:

        i = i + 1
        StrSearchCriteria = "=($W1=" & Chr(34) & "ETF" & Chr(34) & ")"
        With .Range("A:A").FormatConditions
            .Add Type:=2, Formula1:=StrSearchCriteria
            With .Item(i)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = RGB(225, 225, 0)
                End With
                .StopIfTrue = False
            End With
        End With

This does not set the format conditions. The rule is there and the range, criteria are correct, but the format conditions are "No Format Set".

HOWEVER.... if I change

With .Range("A:A").FormatConditions

to

With .Range("E:E").FormatConditions

It works...

"B:B", "C:C", "D:D" all fail with the same issue.

Using "E:E" (and I also tried "F:F" which worked), I can then open the workbook and manually change the range to "A:A", "B:B", "C:C" or "D:D" and it works.

I then tried "A:E", which also worked. It appears that if I use any part of column E through W (last column) it works, but if I don't include anything from E and following it fails.

Totally confused here. Any ideas?

Answer 1

Is there a merged cell range in A:D?

Answer 2

I believe @mer_curius hit on the issue - merged cells.

So, as a work around, since I only want A:A to have the coloring and I need to set the condition to A:E, I follow that rule with setting a rule with the same criteria and no-fill B:E.

The code that works looks like this:

        i = 1
        StrSearchCriteria = "=($W1=" & Chr(34) & "ETF" & Chr(34) & ")"
        strRange = "A:E"
        With .Range(strRange).FormatConditions
            .Add Type:=2, Formula1:=StrSearchCriteria
            With .Item(i)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = RGB(225, 225, 0)
                End With
                .StopIfTrue = False
            End With
        End With
        i = i + 1
        StrSearchCriteria = "=($W1=" & Chr(34) & "ETF" & Chr(34) & ")"
        strRange = "B:E"
        With .Range(strRange).FormatConditions
            .Add Type:=2, Formula1:=StrSearchCriteria
            With .Item(i)
                .SetFirstPriority
                With .Interior
                    .Pattern = xlNone
                End With
                .StopIfTrue = False
            End With
        End With