Conditional Formatting - Use Graphic / Picture?

bob61

New Member
Joined
Jan 16, 2003
Messages
19
I did a search to see if this was asked before and didn't find anything. What I want to do is reference a cell, if the value is 1 display a graphic image of a GREEN light, if 2 display image of YELLOW light and if 3 then display image of RED light.

I know I can use conditional formatting to set a cell's color based on value, but I want to use a more eye appealing graphic image (.jpg) to be displayed.

Any suggestions as to how this is best accomplished?

Thanks!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi bob61,

This can certainly be done with a few lines of VBA code, but I'm not completely clear on how you want this to work.

1. Do you have 3 separate jpeg images, one for each color light?

2. Do you want them to appear inside the cell? If so will you size the cell (row and column) so that the cell is big enough to contain the image?

3. If you want them to appear outside (next to) the cell, then cell size is not an issue. But then will the user ever need to access the cell that is overlapped by the stoplight image?

Damon
 
Upvote 0
Thanks for your response Damnon. Regarding your questions:
1. I have three seperate images (red, yellow and green).

2. Location of the image is either in the cell or cell adjacent, whatever would be easier. I can control the size of the cell, image is relatively small so should not be a problem either way.

3. If image is in next cell user won't have any issue to access the cell that contains the image, this is more for status reporting and will be controlled input by me or someone on my team.

Thanks in advance.
 
Upvote 0
Hi again Bob61,

Okay, here is a fairly simple way to accomplish this:

1. Paste all three pictures onto the worksheet and position them where you want them to appear (whether in-cell or next to it).

2. Name the three pictures "Green", "Yellow" and "Red". To do this, select each picture and type its name in the Name box on the Formula toolbar just above cell A1.

3. Install the code below into the worksheet event code module. To do this right-click on the worksheet tab, select View Code, and paste the code into the VBE Code pane. Here's the code:

Private Sub Worksheet_Calculate()
HideSignals
Select Case [b4]
Case 1: Shapes("Green").Visible = msoTrue
Case 2: Shapes("Yellow").Visible = msoTrue
Case 3: Shapes("Red").Visible = msoTrue
End Select
End Sub

Sub HideSignals()
Shapes("Green").Visible = msoFalse
Shapes("Yellow").Visible = msoFalse
Shapes("Red").Visible = msoFalse
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [b4].Address Then
'this does not calculate the worksheet. It just calls the Calculate event.
Worksheet_Calculate
End If
End Sub

The code is now ready to use. This example is based on cell B4 containing a formula that yields a value 1, 2, or 3. To change to any other cell just change the [b4] references (two places) to your cell of interest. If it is a named cell you can just type the name in the brackets.

Note that this works by making the signal of interest visible while the others are invisible. Even though two of the pictures are always invisible, they always reside on the worksheet, but do not interfere in any way with the worksheet's operation.
 
Upvote 0
Damnon - Kudos for the code, the "hiding" of images should work fine. Just one more thing, there are 6-9 cells that will have status indicators (various components of a project), your solution addresses for a single cell, would you suggest same approach for updating multiple cells?
 
Upvote 0
Hi again Bob61,

Yes, I would use the same approach for as few as 9 instances. If the number was 900 a totally different approach would be called for. Even with 9 it is a toss up whether to code it using a loop, but I think just repeating the code 9 times is just as easy. Here's how I would handle it:

Just for example, let the 9 cells be B2, B4, B6, F2, F4, F6, J2, J4, J6.

1. Name your picture icons "Green1", "Yellow1", "Red1", "Green2", "Yellow2", "Red2", etc., where the number suffixes refer to each of the 9 cells.

2. Modify the code as follows.

Private Sub Worksheet_Calculate()
HideSignals
Select Case [b2]
Case 1: Shapes("Green1").Visible = msoTrue
Case 2: Shapes("Yellow1").Visible = msoTrue
Case 3: Shapes("Red1").Visible = msoTrue
End Select
Select Case [b4]
Case 1: Shapes("Green2").Visible = msoTrue
Case 2: Shapes("Yellow2").Visible = msoTrue
Case 3: Shapes("Red2").Visible = msoTrue
End Select

'etc. for all 9 cells

End Sub

Sub HideSignals()
Dim i As Integer
For i = 1 to 9
Shapes("Green" & i).Visible = msoFalse
Shapes("Yellow" & i).Visible = msoFalse
Shapes("Red" & i).Visible = msoFalse
Next i
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,[B2,B4,B6,F2,F4,F6,J2,J4,J6]) Is Nothing Then
'this does not calculate the worksheet. It just calls the Calculate event.
Worksheet_Calculate
End If
End Sub

_________________________________
Excellerate!

Damon
 
Upvote 0
Damon - thanks! Works like a champ. I'll spend some time reviewing the code and streamlining it for more than 9 status lights - now that this is so "spiffy" I'm sure that management will want to use this more ;)
 
Upvote 0
Help?!

I so am trying to CREATE TRAFFIC LIGHT INDICATORS with these codes... the 1st SINGLE code I can get to change 3 pictures.... BUT the second code I get a DEBUG ERROR saying "The item with the specified name was not found.. then it goes into the VB Editor and highlights "Shapes("Green" & i).Visible = msoFalse"

I tried several ways to troubleshoot this..

1. Added in ALL 9 cells
2. Added and labeled ALL images
3. took out all but 1 image
4. made sure to add/delete BOTH VB code "cell links"(where you would 1,2,3)

DANG IM GOING CRAZY... VB just doesnt make sense sometimes...

I even highlight in VB the formula ["Green" & i] and see it says "i=1" or "i-2" depending on when i checked that out..... it just seems like the code cannot use the DIM or whatever to define the object "label" for the HideSignals command...
 
Upvote 0
Hi sharpescalade,

The error message you are getting indicates there is no shape on the sheet with that name. It is very important that the sheet contain all the shapes Green1...Green9 as if any are missing you will get this error. Also, the name of the shape must be exactly as in the code (with the exception that the names are not case-sensitive). For example you would get this error if the shape were named "Green02" or "Green 2" instead of "Green2", so you should check the green shapes to make sure that you didn't accidentally mistype the name.

I should mention that if you are just using a simple color-filled circle to represent the traffic signal, it is possible to do this without using shapes or code--it can be done with conditional formatting. If interested in how to do this see this posting:

http://www.mrexcel.com/board2/viewtopic.php?t=227742&highlight=conditional+formatting

I hope this helps.

Damon
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top