none
Unable to assign FormatConditions values RRS feed

  • Question

  • Hi,

    Could someone please let me know what I am doing wrong here? In the VBA code below the last two (commented out) FormatConditions assignments do not work although I am able to add them manually via the conditional formatting menu after running this code.

    Sub Add_CF()

        With ActiveSheet

            With .Range("B2:H1000")
                .FormatConditions.Add xlExpression, Formula1:="=$H2=30"
                .FormatConditions(1).Interior.ColorIndex = 6
            End With
            With .Range("B2:H1000")
                .FormatConditions.Add xlExpression, Formula1:="=$H2=50"
                .FormatConditions(2).Interior.ColorIndex = 4
            End With
            With .Range("I2:I1000")
                .FormatConditions.Add xlExpression, Formula1:="=$E2<0"
                '.FormatConditions(3).Interior.ColorIndex = 3
            End With
            With .Range("A2:A1000")
                .FormatConditions.Add xlExpression, Formula1:="=$A2=3"
                '.FormatConditions(4).Interior.ColorIndex = 41
            End With
        End With
    End Sub

                 
    Tuesday, December 6, 2016 9:02 AM

Answers

  • Yes, and for a given range you can add maximum of 3 different conditions.

    If above answer has resolved your issue, kindly mark it as answered in order to keep this forum neat.


    Vish Mishra

    • Marked as answer by Ronbloke123 Tuesday, December 6, 2016 9:41 AM
    Tuesday, December 6, 2016 9:33 AM

All replies

  • Hi, 

    I can see that in I2:I1000 there is only one format, but still you have used .formatConditions(3) and same with A2:A1000.

    here is the correct code:

    Sub Add_CF()
    
        With ActiveSheet
    
            With .Range("B2:H1000")
                .FormatConditions.Add xlExpression, Formula1:="=$H2=30"
                .FormatConditions(1).Interior.ColorIndex = 6
            End With
            With .Range("B2:H1000")
                .FormatConditions.Add xlExpression, Formula1:="=$H2=50"
                .FormatConditions(2).Interior.ColorIndex = 4
            End With
            With .Range("I2:I1000")
                .FormatConditions.Add xlExpression, Formula1:="=$E2<0"
                .FormatConditions(1).Interior.ColorIndex = 3
            End With
            With .Range("A2:A1000")
                .FormatConditions.Add xlExpression, Formula1:="=$A2=3"
                .FormatConditions(1).Interior.ColorIndex = 41
            End With
        End With
    End Sub

     

    Vish Mishra


    Tuesday, December 6, 2016 9:21 AM
  • Brilliant, thanks Vish,

    I'm very not familiar with VBA. So is there a collection of format conditions for each defined range rather that one overall?

    Regards,

    Ron

    Tuesday, December 6, 2016 9:31 AM
  • Yes, and for a given range you can add maximum of 3 different conditions.

    If above answer has resolved your issue, kindly mark it as answered in order to keep this forum neat.


    Vish Mishra

    • Marked as answer by Ronbloke123 Tuesday, December 6, 2016 9:41 AM
    Tuesday, December 6, 2016 9:33 AM