[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional Graphics in Excel

Posted on 2011-03-03
21
Medium Priority
?
541 Views
Last Modified: 2012-05-11
I would like to have graphics appear or not depending on what data are present in an Excel spreadsheet.  If cell A1 contains a 1, a circle appears.  If that cell contains a 2, a square shows up, etc.  Or maybe it's a big circle that changes color depending on what data are in cell A1.

Is anything like this possible?
0
Comment
Question by:RolandGarton
  • 7
  • 7
  • 6
  • +1
21 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033726
>>>Or maybe it's a big circle that changes color depending on what data are in cell A1.

You can create a shape and place it on top of that cell and then in worksheet change event check the value of that cell and then change color of the shape via code.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35033779
Here is an example. In the file attached, if you enter 1 in Cell E10 then the border color of the shape will be black. If it is 2 then it will turn to red.

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E10")) Is Nothing Then
        Shapes("Oval 3").Select
        Select Case Range("E10").Value
        Case 1 '<~~ If cell value is 2 then color Black
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
        Case 2 '<~~ If cell value is 2 then color Red
            Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
        End Select
        Range("E10").Select
    End If
End Sub

Open in new window


Shapes-Example.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35034494
It sounds a little to me like you are doing Conditional Formatting using icons? If so, I would use the built-in features.
If you want to display different pictures based on the value of a cell, you can also do that without code using linked pictures but it's a little more involved than CF. I can demonstrate that if you would like.

Regards,
Rory
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:RolandGarton
ID: 35036709
Linked pictures...could you point me to an explanation or example?  I've not heard of linked pictures.

I tried conditional formatting but could not find a way to control the size, shape, and color of the icons.  Is there a way to to that?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35036734
Have a look here for linked picture samples: http://www.mcgimpsey.com/excel/lookuppics.html

With icon sets you get them as they are - i.e. you can change which of the available ones is displayed, but not alter the properties of each.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35037203
RolandGarton: Is there a specific reason why you ignored my post? It does what you asked in your question? If not, then please let me know so that I can do the necessary changes...

Sid
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35037272
Perhaps he is merely exploring alternatives - the question is not closed after all. ;)
0
 

Author Comment

by:RolandGarton
ID: 35037424
Right...just interested in alternative approaches if any.  Sid, your solution is a partial solution.  It answers my second alternative but not the first in which shape changes from square to circle.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35037426
Yes but the OP can at least acknowledge my post and then look for options. ;-)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35037439
Fair enough Roland.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35037448
>>>>but not the first in which shape changes from square to circle.

Do you want the code for that? I can do that as well...

Sid
0
 

Author Comment

by:RolandGarton
ID: 35037686
I hereby acknowledge all posts and seek absolution for any breach of netiquette.  Also for making fun of my little brother when growing up, but that's outside the scope of this discussion.

Yes, I'd be interested in seeing code that allow for different shapes as well as different colors of the same shape.  I'd also be interested in any solutions that don't involve VBA, but it appears that Excel has no built-in features for changing shape properties based on cell values.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35037734
Using the McGimpsey approach you can return any picture you like based on the cell value. No VBA required - all you have to do is create the initial shapes.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35038007
>>>Yes, I'd be interested in seeing code that allow for different shapes as well as different colors of the same shape.

Here you go. Sample File Attached

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E10")) Is Nothing Then
        Dim oSh As Shape
        Set oSh = Sheets("sheet1").Shapes("Oval 3")
        With oSh
            Select Case Range("E10").Value
            Case 1
                '~~> Change color to black
                .Line.ForeColor.SchemeColor = 64
                '~~> Chnage shape to Rectangle
                .AutoShapeType = msoShapeRectangle
            Case 2
                '~~> Change color to red
                .Line.ForeColor.SchemeColor = 10
                '~~> Chnage shape to Oval
                .AutoShapeType = msoShapeOval
            End Select
        End With
        Range("E10").Select
    End If
End Sub

Open in new window

Shapes-Example.xls
0
 

Author Comment

by:RolandGarton
ID: 35041066
Thanks, SiddharthRout, that will help with my application.

Rorya - The McGimpsey approach specifically says VBA is required, and shows VBA as part of the solution.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35042205
I had forgotten that - it's wrong though. You can do it with no VBA at all using named ranges.
0
 

Author Comment

by:RolandGarton
ID: 35048031
Rorya, can you describe how to accomplish the McGimpsey results using named ranges instead of VBA?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35055000
See example attached. The picture is linked to a named range that uses a formula to look up the correct range to link to.
Linked-picture-demo.xls
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35058182
Rory, This is a beauty. Thanks for sharing.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35058195
No problem - always happy to be of use. :)
0
 

Author Comment

by:RolandGarton
ID: 35058840
Yes, indeed, nifty & elegant.  Lots to learn in this little example.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question