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!