Icon Set Colors?

[Hesham911]

Hi Guru's,

Just wondering if it's possible to change the icon set colors (Excel 2010) in the conditional formation via a VBA please? I know there is a workaround to change the colors via creating a custom table with colored symbols and a smart Lookup's.

Any guidance is really much appreciated.

Thanks in advanced
H

 

[vletm]

Hesham911 - Could this match for You?
http://blog.contextures.com/archives/2013/09/03/create-your-own-excel-icon-set/

 

[Hesham911]

Hesham911 - Could this match for You?
http://blog.contextures.com/archives/2013/09/03/create-your-own-excel-icon-set/

Thanks Vletm for the prompt response. I am using the above method in my spreadsheet already.

I'm just wondering if any solution was introduced via VBA.

I have tried this code, which works fine except the .Color = 7039480?

Sub ApplyIconSetCF()
  Dim rRangeToFormat As Range
  Dim ic As IconCriteria
 
  '--modify to the desired range
  Set rRangeToFormat = Range("C3:D15")
 
  With rRangeToFormat
      'With .FormatConditions
      '--clear any existing CF
        .Delete
        .AddIconSetCondition
        '.Color = 7039480
      End With
      With .FormatConditions(1)
        .SetFirstPriority
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
        '.Color = 7039480
        With .IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = 40
            .Operator = xlGreaterEqual
            '.Color = 7039480
        End With
        With .IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 70
            .Operator = xlGreaterEqual
        End With
      End With
  End With
End Sub

Many thanks,
H

 

[vletm]

Hesham911
Have You tested
.font.colorindex=3
I quickly find that maybe color not work?
maybe with it would work with .font ?
I haven't test this kind ... not yet.

Of course You know this link too:
https://msdn.microsoft.com/library/office/ff196650.aspx

 

[Hesham911]

http://chandoo.org/forum/members/vletm.19418/
Unfortunately .font.colorindex=3 returned with Object doesn't support this property.

I don't think there is a Font or Color property for .IconCriteria?

https://msdn.microsoft.com/en-us/library/office/ff838790.aspx

 

[vletm]

Hesham911
so far those seems to be fixed:
https://msdn.microsoft.com/en-us/library/office/ff193828.aspx
so ... that You used method #2 maybe the quickest now.

 

[Hesham911]

http://chandoo.org/forum/members/vletm.19418/

I have tried to test as much as I can, some returned with out of range. but still can't change the color.

thanks for your help Vletm, I tried my best.

 

[vletm]

Hesham911
IconSet with this info:
https://msdn.microsoft.com/en-us/library/office/ff839630.aspx