Soy muy nuevo en VBA (y en cualquier tipo de programación en general), así que no estoy seguro de cómo proceder aquí. Supongo que mi error tiene que ver con la superposición de rangos para mis formatos condicionales, ya que también recibí errores cuando el código se configuró de una forma diferente que se resolvió una vez que los rangos ya no se superponen. Ese podría no ser el caso aquí, pero pensé que sería útil saberlo.

Recibo un error de 'Subíndice fuera de rango' con el siguiente código:

Sub test2()
    Dim rngToFormat As Range
    Set rngToFormat = ActiveSheet.Range("$a$1:$z$1000")
    Dim rngToFormat2 As Range
    Set rngToFormat2 = ActiveSheet.Range("$k$20:$k$1000")
    Dim rngToFormat3 As Range
    Set rngToFormat3 = ActiveSheet.Range("$j$22:$j$1000")
    Dim rngToFormat4 As Range
    Set rngToFormat4 = ActiveSheet.Range("$i$22:$i$1000")
    Dim rngToFormat5 As Range
    Set rngToFormat5 = ActiveSheet.Range("$g$20:$g$1000")
    Dim rngToFormat6 As Range
    Set rngToFormat6 = ActiveSheet.Range("$d$9, $f$9")
    Dim rngToFormat7 As Range
    Set rngToFormat7 = ActiveSheet.Range("$G$3:$G$7,$G$11:$G$15,$E$3:$E$7,$E$11:$E$15,$N$3:$N$7,$N$11:$N$15,$L$3:$L$7,$L$11:$L$15")
    rngToFormat.FormatConditions.Delete
    rngToFormat.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=if(R[]C20=1, true(), false())"
        rngToFormat.FormatConditions(1).Font.Color = RGB(228, 109, 10)
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""6. Negotiate"", R[]C11<25)"
        rngToFormat2.FormatConditions(2).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""4. Develop"", R[]C11<15)"
        rngToFormat2.FormatConditions(3).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""5. Prove"", R[]C11<20)"
        rngToFormat2.FormatConditions(4).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""7. Committed"", R[]C11<30)"
        rngToFormat2.FormatConditions(5).Font.ColorIndex = 3
    rngToFormat2.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=and(R[]C7=""Closed Won"", R[]C11<35)"
        rngToFormat2.FormatConditions(6).Font.ColorIndex = 3
    rngToFormat3.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlGreater, Formula1:=200
        rngToFormat3.FormatConditions(7).Font.ColorIndex = 3
    rngToFormat4.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlGreater, Formula1:=60
        rngToFormat4.FormatConditions(8).Font.ColorIndex = 3
    rngToFormat5.FormatConditions.Add Type:=xlExpression, _
        Formula1:="=or(R[]C7=""1. Plan"", R[]C7=""2. Create"", R[]C7=""3. Qualify"")"
        rngToFormat5.FormatConditions(9).Font.ColorIndex = 3
    rngToFormat6.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlLess, Formula1:=0
        rngToFormat6.FormatConditions(10).Font.ColorIndex = 3
        rngToFormat6.FormatConditions(10).Interior.Color = RGB(204, 204, 255)
        rngToFormat6.FormatConditions(10).Interior.Pattern = xlSolid
    rngToFormat7.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlLess, Formula1:=0
        rngToFormat7.FormatConditions(11).Font.ColorIndex = 3
        rngToFormat7.FormatConditions(11).Interior.Color = RGB(215, 228, 158)
        rngToFormat7.FormatConditions(11).Interior.Pattern = xlSolid
End Sub

¡Cualquier consejo sería apreciado, gracias!