Using Picture in Conditional Formatting

[tnago]

Hi

I am trying to find a way to show a different background as a part of conditional formatting instead of color. The idea is highligh the cell with a star if the value of the cell meets the criteria. Any help in this regard will be greatly appreciated.

Thanks
Tnago

 

[Oaktree]

Welcome to the board!

You could make your star using autoshapes and insert this code to the worksheet (right click on the sheet tab and go to "view code"):

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Range("B2") = 5 <SPAN style="color:#00007F">Then</SPAN>
ActiveSheet.Shapes("AutoShape 1").Line.Visible = msoTrue
Else: ActiveSheet.Shapes("AutoShape 1").Line.Visible = msoFalse
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[tnago]

Thank you Oaktree for you quick response. However, being not very versed in the VB, I need a little bit of guidiance.

I am using three colors which have value ranges e.g 1-50 green, 51-80 yellow and greater then 81 red. These three conditions are easily done by using the excel conditional formatting feature. However, if the cell value is lowever then say 25 then I want to show a green star with the value.

Can you please advise how the instruction that you have provided, can be integrated with the rest of the conditional formatting.

 

[Oaktree]

It sounds like you could leave the conditional formatting as is and just add the code from my last post to make your star. Just change Range("B2") = 5 to Range("B2") < 25, where B2 is the cell you have conditionally formatted(Autoshapes is part of the drawing toolbar).