Solved

Conditional Graphics in Excel

Posted on 2011-03-03

Microsoft Excel

1 solution

Medium Priority

532 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?

Comment

Question by:RolandGarton

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

21 Comments

LVL 30

Expert Comment

by:SiddharthRout

ID: 350337262011-03-03

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

LVL 30

Expert Comment

by:SiddharthRout

ID: 350337792011-03-03

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

LVL 85

Expert Comment

by:Rory Archibald

ID: 350344942011-03-04

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

Author Comment

by:RolandGarton

ID: 350367092011-03-04

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?

LVL 85

Expert Comment

by:Rory Archibald

ID: 350367342011-03-04

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.

LVL 30

Expert Comment

by:SiddharthRout

ID: 350372032011-03-04

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

LVL 85

Expert Comment

by:Rory Archibald

ID: 350372722011-03-04

Perhaps he is merely exploring alternatives - the question is not closed after all. ;)

Author Comment

by:RolandGarton

ID: 350374242011-03-04

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.

LVL 30

Expert Comment

by:SiddharthRout

ID: 350374262011-03-04

Yes but the OP can at least acknowledge my post and then look for options. ;-)

Sid

LVL 30

Expert Comment

by:SiddharthRout

ID: 350374392011-03-04

Fair enough Roland.

Sid

LVL 30

Expert Comment

by:SiddharthRout

ID: 350374482011-03-04

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

Author Comment

by:RolandGarton

ID: 350376862011-03-04

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.

LVL 85

Expert Comment

by:Rory Archibald

ID: 350377342011-03-04

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.

LVL 30

Accepted Solution

by:SiddharthRout

SiddharthRout earned 500 total points

ID: 350380072011-03-04

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

Author Comment

by:RolandGarton

ID: 350410662011-03-04

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.

LVL 85

Expert Comment

by:Rory Archibald

ID: 350422052011-03-05

I had forgotten that - it's wrong though. You can do it with no VBA at all using named ranges.

Author Comment

by:RolandGarton

ID: 350480312011-03-06

Rorya, can you describe how to accomplish the McGimpsey results using named ranges instead of VBA?

LVL 85

Expert Comment

by:Rory Archibald

ID: 350550002011-03-07

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

LVL 43

Expert Comment

by:Saqib Husain, Syed

ID: 350581822011-03-07

Rory, This is a beauty. Thanks for sharing.

LVL 85

Expert Comment

by:Rory Archibald

ID: 350581952011-03-07

No problem - always happy to be of use. :)

Author Comment

by:RolandGarton

ID: 350588402011-03-07

Yes, indeed, nifty & elegant. Lots to learn in this little example.

Featured Post

Question has a verified solution.

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

MS Excel - using the Hyperlink formula

Article by: Thomas

Do you use a spreadsheet like Microsoft's Excel? Have you ever wanted to link out to a non excel file on your computer or network drive? This is the way I found to do it!

Custom Document Properties in Excel

Article by: Martin

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.

Creating a Double-Sided Bar Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Microsoft Excel

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…