VBA Conditional Formatting is failing to run

[schlacter901]

Hello everyone,

Been stuck on this for a while and thought you guys might be able to help out. I am writing a program and a snippet from fails to run. Basically i have a column of values and blanks that vary from -10 to +10. I wrote this code to flag values of interest. The selection for the conditional formatting includes a variable (this should not matter). The problem is that when my code gets to this snippet it breaks due to a Run-time error '1004'. I cannot for the life of me figure out why this is happening

Sub conditionalboss()
    
    Dim fire As Integer
    'this is dynamic since it loops through multiple sheets of varying length
    fire = 99
    ' for simplicity i defined it as 99 here this is done before in a different part of the code and it is
    ' initialized
    
    Range("A1:A" & fire).Select
    
    Dim cfIconSet As IconSetCondition
    Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
    With cfIconSet
       .ShowIconOnly = True
       .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
    End With
    With cfIconSet.IconCriteria(1)
        .Type = xlValueNumber ' This line will not run
        .Value = 0 ' This line will not run
        .Operator = xlGreater
    End With
    With cfIconSet.IconCriteria(2)
        .Type = xlValueNumber
        .Value = -1
        .Operator = xlGreatedEqual
    End With
    With cfIconSet.IconCriteria(3)
        .Type = xlValueNumber
        .Value = -1
        .Operator = xlLess ' This line will not run
    End With


End Sub

Any help with this or ideas would be much appreciated.

Cheers!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think you need something more like this:

Sub conditionalboss()
    Dim fire As Integer
    Dim cfIconSet As IconSetCondition
    
    'this is dynamic since it loops through multiple sheets of varying length
    fire = 99
    ' for simplicity i defined it as 99 here this is done before in a different part of the code and it is
    ' initialized
    
    Range("A1:A" & fire).Select

    Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
    With cfIconSet
       .ShowIconOnly = True
       .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
        With .IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = 150
            .Operator = xlGreaterEqual
        End With
        With .IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 250
            .Operator = xlGreaterEqual
        End With
    End With
End Sub

I am not sure what your ranges were supposed to be but mine are:
<150
>= 150 & <250
>= 250

I used the Macro Recorder and that let IconCriteria(1) default.
You also have some invalid constants in your code: e.g xlGreatedEqual and xlValueNumber.

When you use the dialog screens to set up the conditional formatting there are some settings that it won't let you enter. If you try and code these using VBA they will not execute.

 

[schlacter901]

Hey RickXL,

I took a look at what you did and applied it to my code. It seems that I was setting up the condition fences backwards to what excel was expecting. I was able to get it working with this code. The constants were just typos as I was using the values for the constants instead of the names. Anyways, thanks for the help it was much appreciated.

 Dim cfIconSet As IconSetCondition        Set cfIconSet = Selection.FormatConditions.AddIconSetCondition
        With cfIconSet
           .ShowIconOnly = True
           .IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
        End With
        With cfIconSet.IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = -1
            .Operator = xlGreaterEqual
        End With
        With cfIconSet.IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = -1
            .Operator = xlGreaterEqual ' This line will not run
        End With

The final code in case anyone else has the same issue.

Cheers!

 

[RickXL]

No problem and thanks for the feedback.

Regards,