FormatConditions(????)

[ExcelVBANOOB]

As I fight through teaching myself VBA (I interviewed for an engineering internship, and when they asked if I could write my own macros, I responded with a 1000-yard stare, so before I start next week, I need to be a VBA wizard), I've recorded a few macros for use in my programs. However, in my efforts to simplify them, I see a bunch of parenthetical numbers after the "FormatConditions" and "IconCriteria" terms. Often, those numbers are the only differences between the "With-EndWith" statements. I realize in a jam, I can just copy/paste those terms, but I'd like to know what I'm doing, and I'm a bit of a perfectionist. I've tried VBA help and internet searches, but can't seem to figure out what those numbers stand for. Please help! Thanks a ton in advance!

 

[Norie]

Can you post the code so we can see these numbers you are referring to?

 

[ExcelVBANOOB]

Yes. Sorry about that.

ActiveCell.Range("A1:A4").Select    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3ArrowsGray)
    With Selection.FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
    With Selection.FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 5
    End With

 

[Norie]

If you mean the 7 and 5 then they represent the different logical operators being used in the conditional formatting.

In this case 7 is for > and 5 is for >=.

 

[ExcelVBANOOB]

Awesome! I was curious about those, but assumed they were something similar. Do you know if there's a listing or table somewhere that lists the other operators and their numeral-equivalents?

In the following code:

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(ARGVALUE-1).IconSet = ActiveWorkbook.IconSets(xl3ArrowsGray)
    With Selection.FormatConditions(ARGVALUE-1).IconCriteria(ARGVALUE-2)

What do the parenthetical numbers (preceded by "ARGVALUE-" in this code-snippet) stand for? That is: Why isn't there a "(1)" after ALL the "FormatConditions," and why isn't there an "IconCriteria(1)" (when from the code, in my last post, "IconCriteria" is followed by both "(2)" and "(3)")?

 

[Norie]

In FormatConditions(n) n is the nth conditional formatting rule.

In this section of the code Selection.FormatConditions.Count is used because you have added a new conditional formatting rule.

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

In the rest of the code you are changing the criteria etc for a specific rule.

IconCriteria(2) and IconCriteria(3) are for the 2nd and 3rd criteria of the rule.

Not 100% sure why there is no IconCriteria(1) but I think it has something to do with the use of icon sets for the formatting and one of the icons not having criteria you can specify

You can actually add this,

   With Selection.FormatConditions(1).IconCriteria(1)
        .Icon = xlIconRedCross
   End With

and the icon will change, but this will error.

   With Selection.FormatConditions(1).IconCriteria(1)
        .Icon = xlIconRedCross
        .Type = xlConditionValueNumber
        .Operator = 7
    End With

 

[ExcelVBANOOB]

Ooooooooh. *lightbulb* That makes sense. Thank you so very much, Norie! I really appreciate the help!