Hide conditional Icon set

[jerry.rozario]

Hello there,
First of all thanks all of you who always help to find my critical solutions. This time I’ve a excel sheet contains conditional formatting with 3 set of traffic icon.
Each and every column has their own formula to generate the result. So I cannot delete or change the conditional formatting. And I don’t want also. . I want to hide those red dots / icon while the cell is blank. If there is any solution please help me…

 

[balajayan]

Hi jerry.rozario,

Create a new condition with the following:

Condition Formatting -> Format only cells that contain -> Blanks ("Format only cells with")

Click ok. Also check "Stop If True". This condition should precede the traffic signal condition.

Hope this works for you.

 

[jerry.rozario]

Hi,
That's not working as i say there are no cells are blank. all are generate with formula and there is a value (date). and if i try to do with my "No Blanks" option then it just change whole not the red spot's. as example

<TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=117 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59 0.5pt solid; BORDER-LEFT: #9bbb59 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=117 height=34>12/30/01</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" height=51>12/30/00</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" height=34>12/30/01</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" height=51>12/30/01</TD></TR><TR style="HEIGHT: 102pt" height=136><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 102pt; BACKGROUND-COLOR: transparent" height=136>11/10/12</TD></TR><TR style="HEIGHT: 102pt" height=136><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 102pt; BACKGROUND-COLOR: transparent" height=136>3/13/12</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" height=34>12/30/00</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl65 style="BORDER-RIGHT: #9bbb59 0.5pt solid; BORDER-TOP: #9bbb59; BORDER-LEFT: #9bbb59 0.5pt solid; BORDER-BOTTOM: #9bbb59 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" height=51>12/30/01</TD></TR></TBODY></TABLE> in there all cells are farmated with red dots except the date 11/10/12 , 3/13/12 because those are green. I dont want to change the green one. Only red dots dates need to hide. I hide with it by changing the color of the letter but the red dot i couln't. Is there any idea...

 

[balajayan]

Hi jerry.rozario,

Goto Home -> Conditional Formatting -> Manage Rules

Select the condition 'Icon Set' and click 'Edit Rule'. There you will find condition for Green and Yellow. Take the condition for Red.

Then instead of Blank condition, as explained earlier, use the above condition. Then Red will not come.

For example: Icon Set is created as below:
Green: >= 67 Percent
Yellow: >= 33 Percent
Red: < 33 Percent.

In that case, create a condition as follows:
New Rule -> Format only cells that contain -> Cell Value - less than - 33%.
Click OK. Check 'Stop If True'.

Please note: This condition should precede the traffic signal condition.

Hope this should solve your problem.

 

[jerry.rozario]

Wow thank you very much balajayan for your suggestion. it works.