Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Conditional Formatting - Use Graphic / Picture?

This is a discussion on Conditional Formatting - Use Graphic / Picture? within the Excel Questions forums, part of the Question Forums category; I did a search to see if this was asked before and didn't find anything. What I want to do ...

  1. #1
    New Member
    Join Date
    Jan 2003
    Posts
    19

    Default Conditional Formatting - Use Graphic / Picture?

    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!

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,163

    Default Re: Conditional Formatting - Use Graphic / Picture?

    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

  3. #3
    New Member
    Join Date
    Jan 2003
    Posts
    19

    Default Re: Conditional Formatting - Use Graphic / Picture?

    Thanks for your response ****on. 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.

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,163

    Default Re: Conditional Formatting - Use Graphic / Picture?

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm
    (My other life: http://damonostrander.com )

  5. #5
    New Member
    Join Date
    Jan 2003
    Posts
    19

    Default Re: Conditional Formatting - Use Graphic / Picture?

    ****on - 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?

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,163

    Default Re: Conditional Formatting - Use Graphic / Picture?

    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

  7. #7
    New Member
    Join Date
    Jan 2003
    Posts
    19

    Default Re: Conditional Formatting - Use Graphic / Picture?

    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

  8. #8
    New Member
    Join Date
    Sep 2006
    Posts
    5

    Default 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...

  9. #9
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,163

    Default

    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/viewto...nal+formatting

    I hope this helps.

    Damon

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default

    Here is an example of how to update a picture using a workbook name without the need for VBA code. There is an explanation in the attached workbook.

    UpdatePicUsingWorkbookName.zip

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com