Conditional Formatting - Use Graphic / Picture?

[bob61]

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!

 

[erik.van.geit]

Hi, Tom,

This thread was in my library for a while. Today I needed to implement this in a project. There is a drawback using the "choose" function in your example: it is difficult to edit. I'm sure you are aware of that and you would find a solution, but as I constructed one: here it is:

Assuming your workbooks has 2 sheets: Sheet1 & Pics
Define Name
PicReferences
=OFFSET(Pics!$B$1,0,0,COUNTA(Pics!$B:$B),1)

Define Name
ThisPic
=IF(ISNA(MATCH(Sheet1!$B$3,PicReferences,0)),Pics!$A$1,INDIRECT("Pics!$A"&MATCH(Sheet1!$B$3,PicReferences,0)))

Validate cell B3
Allow: List
Source: =PicReferences

Click picture and write formula =ThisPic
You can add photos on sheet Pics
Add reference in column B for each Pic

Formula in E12 (footer for Pic): =INDEX(OFFSET(PicReferences,0,1),MATCH(B3,PicReferences,0))

best regards,
Erik

I can email the example to you if you want.

 

[canaanp]

I have a problem that I have been pulling my hair out over the past few days for. I thought perhaps hidden shapes code could help me, but I'm having trouble with it. I know this post is old, but I hoped someone could still help me. I'm using Excel to interface with a PLC. I have macros assigned to a start button and stop button for 10 motor starters. What you see in the excel file is just MS1, MS2, etc, a start and stop button for each one, and a status light On or OFF. The buttons and status lights are just shapes with the bezel effect and filled different colors. I got the hidden shapes code to work great when I just had one starter. Now I'm trying to incorporate the other 9 starters and I can't get the code right. If I try to name a shape OFF1 or RED1, it jumps to a cell named that like a mile down the line... can someone help me? I can post my file if needed or copy paste the code I have, just let me know... thank you.

 

[Gingertrees]

Problem with my tinkering of Case VBA- ***help***?

I did a simplified version of this, to place a large hard-to-miss picture over most of a worksheet if the user did NOT select something from a certain dropdown in cell G60.
So if Option 1 or Option 2 are selected, all is well and the picture is hidden. If they clear the cell, picture is visible. It works EXCEPT right when the workbook is opened. Then the pic is visible regardless of the contents of G60. What am I missing?

Private Sub Worksheet_Calculate()
'////in the worksheet module/////
Call HidePic
Select Case ActiveSheet.[G60]
Case "Option 1"
    Shapes("SelectPic").Visible = msoFalse
Case "Option 2"
    Shapes("SelectPic").Visible = msoFalse
Case ""
    Shapes("SelectPic").Visible = msoTrue
End Select
End Sub
'--------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [G60].Address Then
'this does not calculate the worksheet. It just calls the Calculate event.
Worksheet_Calculate
End If
End Sub
'--------------------------------
Sub HidePic()
'///placed in a standard module so I can modify/call with a diff. sheet
With Sheet35
.Shapes("SelectPic").Visible = False
End With
End Sub

 

[JasmineL]

I've been trying different versions of code to get my "stoplight" pictures to appear in merged cells based on specific criteria being entered in a cell (the cell on which the picture should be placed has a formula looking at another cell to see if a certain color is listed. if the color is listed, the corresponding picture to this color (in a PicTable) is pulled in). The problem I find is this code below only works for one instance of the color being found:

<code>
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim myRow As Long
Me.Pictures.Visible = False
myRow = 5
While myRow < 123 'as long as myRow is smaller than 123, go through the next section of code.
For Each oPic In Me.Pictures
If oPic.Name = Cells(myRow, 9).Text Then 'I is 9th letter of Alphabet = Column I
oPic.Visible = True
oPic.Top = Cells(myRow, 9).Top + (Cells(myRow, 9).MergeArea.Height / 2) - (oPic.Height / 2)
oPic.Left = Cells(myRow, 9).Left + (Cells(myRow, 9).MergeArea.Width / 2) - (oPic.Width / 2)
Exit For ' Exit the for loop - no need to waste time checking for more matches - we found the pic we wanted
End If 'end of "If oPic.Name = Cells(myRow, 9).Text"
Next oPic 'end of "For Each OPic In Me.Pictures"
myRow = myRow + 3 'add 3 to the Row we're looking at and then loop through again
Wend
End Sub
</code>

I can have upwards of 70 different instances in which I need these pictures pulled in based on criteria (all displayed at the same time). Damon mentioned a loop, but I'm not sure how to have this work with only three designated pictures (I don't want to have green 1...green70).

Thoughts?

 

[arkusM]

Maybe I am missing the obvious but have you simply tried commenting out the Exit For?

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Dim myRow As Long
    Me.Pictures.Visible = False
    myRow = 5
    While myRow < 123    'as long as myRow is smaller than 123, go through the next section of code.
        For Each oPic In Me.Pictures
            If oPic.Name = Cells(myRow, 9).Text Then    'I is 9th letter of Alphabet = Column I
                oPic.Visible = True
                oPic.Top = Cells(myRow, 9).Top + (Cells(myRow, 9).MergeArea.Height / 2) - (oPic.Height / 2)
                oPic.Left = Cells(myRow, 9).Left + (Cells(myRow, 9).MergeArea.Width / 2) - (oPic.Width / 2)
               [COLOR=red][B][COLOR=green] [/COLOR][SIZE=3][COLOR=green]'[/COLOR] [/SIZE]Exit For    '[/B] Exit the for loop - no need to waste time checking[/COLOR][COLOR=red] for more matches - we found the pic we wanted[/COLOR]
            End If    'end of "If oPic.Name = Cells(myRow, 9).Text"
        Next oPic    'end of "For Each OPic In Me.Pictures"
        myRow = myRow + 3    'add 3 to the Row we're looking at and then loop through again
    Wend
End Sub

 

[djk957]

I have instered the VBA code into my Excel worksheet that Damon provided. When it runs, I get a "Run-Time Error 5, Invalid Procedure or Argument".

When I step into the code, I encounter the error at this line - Shapes("Green").Visible = msoFalse

Code encounters error at each occurrence of "msoFalse".

I'm not a VBA coder, just wanted to barrow this functionality and get it to work.

Seems as though the "msoFalse" is not something that Excel VBA knows about.

Any suggestions how to get this code to work? This green/yellow/red traffic light thing is exactly what I would like to do.

I'm runnin MS Excel 2007 SP2.

 

[larrythestag]

Hi Damon,

I know this is 4 years later but I am wondering if you could expand upon what you would do if you have 100+ cells that will be reffering to either 'Green' 'Yellow' or 'Red'.

I would love to use a drop down in E5 to choose either G,Y,R and display the images in F5.

Thanks for your help.