Trying to understand conditional formatting using an exisiting vb.net project in VSTO
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