conditional formating with Icon set

[jvlavl]

Hello,

I'm completely new to VBA
i'm trying to get a set of indicators at the first available column in every sheet that the workbook contains.
the column that is used for the verification is in percentage
I have tried it with the macro recorder and several other things but I can't get it right

the number of columns for each sheet is the same

[TABLE="width: 500"]
<tbody>[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD]50[/TD]
[TD]40[/TD]
[TD]90%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]45[/TD]
[TD]95%[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

50% = good
10% = warning
5% = red

Can someone help me here?

Rgds,

John

 

[oldbrewer]

how is 50% constructed ie is it text 50 plus text %
is it number 50 plus text %
is it .5 formatted as a percentage

 

[jvlavl]

Hello,

It's in number and the column is formatted as percentage.

Rgds,

John

 

[oldbrewer]

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]text[/TD]
[TD="width: 64"]text[/TD]
[TD="width: 64"]text[/TD]
[TD="width: 64, align: right"]50[/TD]
[TD="width: 64, align: right"]25[/TD]
[TD="class: xl22, width: 64, align: right"]50%[/TD]
[TD="class: xl22, width: 64, align: right"]50%[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="class: xl22, align: right"]80%[/TD]
[TD="class: xl22, align: right"]20%[/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]45[/TD]
[TD="class: xl22, align: right"]90%[/TD]
[TD="class: xl22, align: right"]10%[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]46[/TD]
[TD="class: xl22, align: right"]92%[/TD]
[TD="class: xl22, align: right"]8%[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]text[/TD]
[TD]text[/TD]
[TD]text[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]49[/TD]
[TD="class: xl22, align: right"]98%[/TD]
[TD="class: xl22, align: right"]2%[/TD]
[TD]R[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]I think your maths is wrong but whatever the last column[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]meets your icon requirement[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]easy to replace G,W,R with icons eg arrows and to [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]color them using conditional formatting.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]are the numbers I have put in correct ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jvlavl]

Hi,

Yes you are write.
can you help me with the code?

Rgds,

John

 

[oldbrewer]

col G is 1-col F so in G1 put =1 - F1 and drag down

in H1 put =if(g1>.1,"G",if(G1>>05,"W","R"))

in H1 put in the conditional formatting and use format painter for the rest of col H