title: Run-time error '9' Subscript out of range with Conditional Format code
body: <p>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.</p>
  
  <p>I get a 'Subscript out of range' error with the following code:</p>
  
  <pre><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&lt;25)"
          rngToFormat2.FormatConditions(2).Font.ColorIndex = 3
      rngToFormat2.FormatConditions.Add Type:=xlExpression, _
          Formula1:="=and(R[]C7=""4. Develop"", R[]C11&lt;15)"
          rngToFormat2.FormatConditions(3).Font.ColorIndex = 3
      rngToFormat2.FormatConditions.Add Type:=xlExpression, _
          Formula1:="=and(R[]C7=""5. Prove"", R[]C11&lt;20)"
          rngToFormat2.FormatConditions(4).Font.ColorIndex = 3
      rngToFormat2.FormatConditions.Add Type:=xlExpression, _
          Formula1:="=and(R[]C7=""7. Committed"", R[]C11&lt;30)"
          rngToFormat2.FormatConditions(5).Font.ColorIndex = 3
      rngToFormat2.FormatConditions.Add Type:=xlExpression, _
          Formula1:="=and(R[]C7=""Closed Won"", R[]C11&lt;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
  </code></pre>
  
  <p>Any advice would be appreciated, thanks!</p>