Trying to understand conditional formatting using an exisiting vb.net project in VSTO

Jonathan Brotto 1,076 Reputation points
2022-01-19T16:14:35.3+00:00

I am working on an existing vb.net project but there is no documentation I could find that would allow me to directly translate and modify the type of conditioning as I get the default red green circles and I wish to use the 3-arrow iconset. Tried recording a macro but not sure how to translate.
I manage to add and set my rules, but comment out the icons I want along with a few other rules.

        With customerYearRng  
            .FormatConditions.AddIconSetCondition()  
  
            '.FormatConditions(Excel.IconSetCondition.IconSet) = "xl3Triangles" XlConditionValueTypes.xlConditionValueNumber(1))  
            '  Microsoft.Office.Interop.Excel.XlConditionValueTypes.xlConditionValueNumber  
            '.IconSetCondition.IconSet.IconSets = "xl3Triangles"  
            '.FormatConditions(FormatConditions.Count).SetFirstPriority  
            '.FormatConditions(1)  
            '.FormatConditions.ReverseOrder = False  
            '.FormatConditions.ShowIconOnly = False  
            '.FormatConditions.IconSet.IconSets = "xl3Triangles"  
  
            With .FormatConditions(1).IconCriteria(2)  
  
                .Type = Excel.XlConditionValueTypes.xlConditionValueNumber  
                .Value = 0  
                .Operator = 7  
            End With  
            With .FormatConditions(1).IconCriteria(3)  
  
                .Type = Excel.XlConditionValueTypes.xlConditionValueNumber  
  
                .Value = 0  
                .Operator = 5  
            End With  
        End With  

used different pages but not familiar with set and how to translate VBA, C# or old school VB
https://learn.microsoft.com/en-us/office/vba/api/excel.formatconditions.add

https://learn.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/bb286672(v=office.11)?redirectedfrom=MSDN
Best I found but vb.net does not use let or get statements.
Sub CreateIconSetCF()
Dim cfIconSet As IconSetCondition

' Fill cells from C1 to C12 with sample data.  
With ActiveSheet  
   .Range("C1") = 55  
   .Range("C2") = 92  
   .Range("C3") = 88  
   .Range("C4") = 77  
   .Range("C5") = 66  
   .Range("C6") = 93  
   .Range("C7") = 76  
   .Range("C8") = 80  
   .Range("C9") = 79  
   .Range("C10") = 83  
   .Range("C11") = 66  
   .Range("C12") = 74  
End With  
  
Range("C1:C12").Select  
  
' Create an icon set conditional format for the created sample data range.  
Set cfIconSet = Selection.FormatConditions.AddIconSetCondition  
  
' Change the icon set to a 5-arrow icon set.  
cfIconSet.IconSet = ActiveWorkbook.IconSets(xl5Arrows)  
  
' The IconCriterion collection contains all the icon criteria. By indexing into  
' the collection, you can modify each criteria. The following sections set  
' the criteria for each of the arrows in the set.  
With cfIconSet.IconCriteria(1)  
   .Type = xlConditionValueNumber  
   .Value = 0  
   ' XlFormatConditionOperator enumeration that specifies "greater than   
   ' or equal to."  
   .Operator = 7  
End With  
With cfIconSet.IconCriteria(2)  
   .Type = xlConditionValueNumber  
   .Value = 60  
   .Operator = 7  
End With  
With cfIconSet.IconCriteria(3)  
   .Type = xlConditionValueNumber  
   .Value = 70  
   .Operator = 7  
End With  
With cfIconSet.IconCriteria(4)  
   .Type = xlConditionValueNumber  
   .Value = 80  
   .Operator = 7  
End With  
With cfIconSet.IconCriteria(5)  
   .Type = xlConditionValueNumber  
   .Value = 90  
   .Operator = 7  
End With  
  
End Sub  
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,585 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,539 questions
0 comments No comments
{count} votes