1
votes

I'm very new to VBA (and any sort of programming in general), so I'm not sure how to proceed here. I'm guessing my error has something to do with overlapping ranges for my conditional formats as I also got errors when the code was set up a different way that were resolved once the ranges no longer overlapped. That might not be the case here, but I figured it'd be helpful to know.

I get a 'Subscript out of range' error with the following code:

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

Any advice would be appreciated, thanks!

1
which string exactly gives you an error? - Peter L.
I think you need to translate your formulas to A1 style (instead of R1C1). - Peter Albert
@Peter Albert. Hi, I get the error with both A1 and R1C1 style. I'm using r1c1 because A1 style screws up the format range based on active cell. - Sam WB
@PeterL. The specific string it references is rngToFormat2.FormatConditions(3).Font.ColorIndex = 3 I suspect the errors I'm getting might have something to do with format priority? Not sure I'm just sort of throwing out ideas - Sam WB
@PeterL. I tried adding priorities and got the same error, so scratch that as being the problem... I really think it has something to do with overlapping ranges but I have no idea how to fix it Also, I tried separating each range into a different macro... when I run the macro for the 1st range it works, when I run the macro for the 2nd range, I get the same error and it screws up the format set by the first macro (for the 1st range) - Sam WB

1 Answers

1
votes

There are two problems with your code:

  1. You only delete the conditional formats for the first range - but add conditions to all ranges - and later access a specific one that most likely is not the one you just created (FormatConditions(3))
  2. The formulas you entered are the default english formulas - for some stange reason, FormatConditions.Add requires the local formulas though.

I reworked your code, take a look if it solves your problem:

Sub test2()

    fctApply rng:=Range("$a$1:$z$1000"), strFormulaR1C1:="=(R[]C20=1)", dblRGB:=RGB(228, 109, 10), blnDeleteOldConditions:=True

    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""6. Negotiate"",R[]C11<25)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""4. Develop"", R[]C11<15)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""5. Prove"", R[]C11<20)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""7. Committed"", R[]C11<30)", intColorIndex:=3
    fctApply rng:=Range("$k$20:$k$1000"), strFormulaR1C1:="=and(R[]C7=""Closed Won"", R[]C11<35)", intColorIndex:=3

    fctApply rng:=Range("$j$22:$j$10000"), strFormulaR1C1:=200, intType:=xlCellValue, intOperator:=xlGreater, intColorIndex:=3

    fctApply rng:=Range("$i$22:$i$1000"), strFormulaR1C1:=60, intType:=xlCellValue, intOperator:=xlGreater, intColorIndex:=3

    With fctApply(rng:=Range("$g$20:$g$1000"), strFormulaR1C1:=0, intType:=xlCellValue, intOperator:=xlLess, intColorIndex:=3)
        .Interior.Color = RGB(204, 204, 255)
        .Interior.Pattern = xlSolid
    End With

    With fctApply(rng:=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"), strFormulaR1C1:=0, intType:=xlCellValue, intOperator:=xlLess, intColorIndex:=3)
        .Interior.Color = RGB(215, 228, 158)
        .Interior.Pattern = xlSolid
    End With
End Sub

Private Function fctApply(rng As Range, _
    strFormulaR1C1 As Variant, _
    Optional intType As XlFormatConditionType = xlExpression, _
    Optional intOperator As XlFormatConditionOperator, _
    Optional intColorIndex As Integer = -1, _
    Optional dblRGB As Double = -1, _
    Optional blnDeleteOldConditions As Boolean = False _
    ) As FormatCondition

    Dim objCond As FormatCondition
    Dim strFormula As String

    If blnDeleteOldConditions Then rng.FormatConditions.Delete

    strFormula = Application.ConvertFormula(strFormulaR1C1, xlR1C1, xlA1)

    On Error GoTo ConvertLocal
    If intOperator <> 0 Then
        rng.FormatConditions.Add Type:=intType, _
            Formula1:=strFormula, Operator:=intOperator
    Else
        rng.FormatConditions.Add Type:=intType, _
            Formula1:=strFormula
    End If
    On Error GoTo 0
    Set objCond = rng.FormatConditions(rng.FormatConditions.Count)
    If intColorIndex <> -1 Then
        objCond.Font.ColorIndex = intColorIndex
    ElseIf dblRGB <> -1 Then
        objCond.Font.Color = dblRGB
    End If
    Set fctApply = objCond

    Exit Function
ConvertLocal:
    With Range("A1") 'change this to an empty cell address - it is temporarily used to translate from local to normal formulas
        .Formula = strFormula
        strFormula = .FormulaLocal
        .Formula = ""
    End With
    Resume
End Function