Excel VBA - conditional formatting, Index not included in the interval

user_9213166 0
Viewed 192

I have this code that gives me the error Index not included in the interval in line .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

  With students.Range(MyNegRange)
 
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                          Formula1:="=""x""", Formula2:="=""x"""
    
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13311 'rosso
        .TintAndShade = 0
    End With

    .FormatConditions(1).StopIfTrue = False
 
  End With

I want to color in red the cell containing an "x" but only in the specific range. I used the macro recorder and then tried to adjust the code, I don't know why I'm getting the error.

vba excel
1 Answers

You can make your code more readable by pushing the FormatConditions.Add up into the With block:

With students.Range(MyNegRange).FormatConditions.Add( _
                        Type:=xlCellValue, Operator:=xlBetween, _
                        Formula1:="=""x""", Formula2:="=""x""")
    .SetFirstPriority
    With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13311
        .TintAndShade = 0
    End With
    .StopIfTrue = False
 
End With

That way the rest of the code is scoped to the just-added FormatCondition object

user_4788840
Related