Avatar of Roland Garton
Roland Garton
 asked on

Conditional Graphics in Excel

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?
Microsoft Excel

Avatar of undefined
Last Comment
Roland Garton

8/22/2022 - Mon
SiddharthRout

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

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Roland Garton

ASKER
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?
Rory Archibald

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

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

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

ASKER
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.
SiddharthRout

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

Sid
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SiddharthRout

Fair enough Roland.

Sid
SiddharthRout

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

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.
ASKER CERTIFIED SOLUTION
SiddharthRout

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Roland Garton

ASKER
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.
Rory Archibald

I had forgotten that - it's wrong though. You can do it with no VBA at all using named ranges.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Roland Garton

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

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

Rory, This is a beauty. Thanks for sharing.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

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

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