VBA Conditional formatting subscript out of range

Question

Very new to VBA and been working on trying to fix a subscript out of range for hours on the line: .FormatConditions(1).Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="=0.5" What am I missing?

With ActiveSheet
Columns(4).Select
Range("D3").Activate
With Columns("D:D")
    .FormatConditions.Delete
    .FormatConditions(1).Add Type:=xlCellValue, Operator:=xlLess, _
         Formula1:="=0.5"
    .FormatConditions(1).NumberFormat = "0.000"
    .FormatConditions(1).StopIfTrue = False
    .FormatConditions(2).Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
         Formula1:="=0.5"
   .FormatConditions(2).NumberFormat = "#,##0.0"
   .FormatConditions(2).StopIfTrue = False
ActiveSheet.Next.Select

End With
End With
End Sub

score 0 · Answer 1 · answered Apr 12 '18 at 18:30

0

Problem with your syntax when adding conditions. You can also dispense with the Selects I think.

With ActiveSheet.Columns("D:D")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
          Formula1:="=0.5"
     .FormatConditions(1).NumberFormat = "0.000"
     .FormatConditions(1).StopIfTrue = False
     .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, _
          Formula1:="=0.5"
    .FormatConditions(2).NumberFormat = "#,##0.0"
    .FormatConditions(2).StopIfTrue = False
End With

answered Apr 12 '18 at 18:30

SJR

22,986
6
18
26

Thank you so much! So only after I add the format condition do I need to refer to it with the (1). The selects were all about me trying everything I found. – Dave Apr 12 '18 at 18:52
Yes, though there are different approaches you can take. On Select, read [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|240.8343). – SJR Apr 12 '18 at 18:59

VBA Conditional formatting subscript out of range

1 Answers1