VBA- is it possible to set condition based on traffic lights system ( Green light, red light yellow light etc) on a table with VBA?

[kidult7]

Hi, can VBA recognize the icon set on a table based off the light of the icon set?

I have a table that was already conditionally formatted with traffic light system based on certain creteria ( Red, Yellow or Red).
Now I would like to set a loop condition based on the traffic light off the table, is it feasible?

i.e the conditions are the following:
If traffic light is green, then corresponding cell in particular Column AJ is "N";
if traffic light is Red or Yellow, then corresponding cell in particular Column AJ is "Y"

Otherwise, "N"


Will using the below be feasible to set up with the condition at VBA?
xlIconGreenTrafficLight

 

[Zot]

You have to specific by what you meant by icon. Please click Xl2BB icon and install AddIn so that you can use to copy and paste how your worksheet looks like. This way will help others to understand better.

 

[kidult7]

test (1).xlsm
	A	B				F													S	T	U	V	W	X												AJ
3						Unit of Measure													Nov 2020	Dec 2020	Q1 2021	Feb 2021	Mar 2021	Q2 2021												Check (Y/N)
4	M	>=				%													10000%	100%	100%	100%	100%	100%												N
5	M	>=				%													100%	100%	97%	100%	100%	100%												N
6	M	>=				%													100%	100%	100%	100%	100%	100%												N
7	M	<=				%													0%	14%	13%	22%	20%	18%												Y
8	M	<=				%													0%	50%	0%	0%	0%	0%												N
9	M	<=				%													0%	0%	0%	0%	0%	0%												Y
10	M	<=				%													NA	0%	0%	0%	0%	0%												Y
Yellow & Red

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S4:AH6	Other Type	Icon set		NO
S7:AH7	Other Type	Icon set		NO
S8:AH8	Other Type	Icon set		NO
S9:AH9	Other Type	Icon set		NO
S10:AH10	Other Type	Icon set		NO

 

[kidult7]

the code does not show the traffic light icon, I have attached an image for an reference.
Again, is it possible to set condition based on the icon set of the colour based on the table from VBA?


Thank you

 

[kidult7]

to be more specific, I have included my drafted code below, but it doesn't seem to work:


If UCase(ws.Cells(row, "B").Value) = "M" Then
If ws.Cells(row, locate_month.Column) = "" Then
ws.Cells(row, Check.Column) = "N"
ElseIf ws.Cells(row, locate_month.Column) = "N/A" Then
ws.Cells(row, Check.Column) = "N"
ElseIf ws.Cells(row, locate_month.Column).FormatCondition(1).Icon = xlIconGreenTrafficLight Then
ws.Cells(row, Check.Column) = "N"

ElseIf ws.Cells(row, lastmonth.Column).FormatCondition(1).Icon = xlIconRedTrafficLight Then
ws.Cells(row, Check.Column) = "Y"

 

[Zot]

Why not just use Conditional Formatting instead of using VBA? Do you want something similar to this?

 

[kidult7]

the spreadsheet have been set with the conditional formatting based on the values with the icon set already as my imagine attached above Now I would like to set up a vba to looked up the the colour of the icon set for other work.. I need to make it dynamic to make it able to look at different columns/month just want to see if that’s feasible

 

[Zot]

the spreadsheet have been set with the conditional formatting based on the values with the icon set already as my imagine attached above Now I would like to set up a vba to looked up the the colour of the icon set for other work.. I need to make it dynamic to make it able to look at different columns/month just want to see if that’s feasible

Now I think I understood what you were trying to achieve.

I believe it is possible. I don't know the correct syntax since never done it before. Why not just read the cell value instead as reference instead of the icon criteria or state?

 

[kidult7]

Because the condition varies from each row and hence preset the conditional formatting already . Now I just want to filter out those that need attention of ( red or yellow ) from the icon traffic light

 

[Zot]

Because the condition varies from each row and hence preset the conditional formatting already . Now I just want to filter out those that need attention of ( red or yellow ) from the icon traffic light

I found out that your xlIconGreenTrafficLight is not for the icon you used. That xlIconGreenTrafficLight is for traffic light with square box

You can get the xlIcon numeration using command below for example

Debug.Print ws.Range("T4").FormatConditions(1).IconCriteria(1).Icon

Note:
ws is just the worksheet I defined
For traffic light with 3 colors, the IconCriteria index would be 1, 2, and 3

So you can have something like this

If ws.Range("T4").FormatConditions(1).IconCriteria(3).Icon = xlIconGreenTrafficLight Then
    ' your code here
End If

IconCriteria(3) would be have enumeration 14 (xlIconGreenTrafficLight) for those square box traffic light