For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Course Of The Month
6 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Conditional Graphics in Excel
Posted on 2011-03-03
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?
Is anything like this possible?
[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
7
7-
6
- +1
21 Comments
>>>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
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
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
Shapes-Example.xls
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
Shapes-Example.xls
Active 2 days ago
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
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
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?
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?
Active 2 days ago
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.
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.
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
Sid
Active 2 days ago
Perhaps he is merely exploring alternatives - the question is not closed after all. ;)
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.
>>>>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
Do you want the code for that? I can do that as well...
Sid
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.
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.
Active 2 days ago
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.
>>>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
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
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.
Rorya - The McGimpsey approach specifically says VBA is required, and shows VBA as part of the solution.
Active 2 days ago
I had forgotten that - it's wrong though. You can do it with no VBA at all using named ranges.
Active 2 days ago
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
Linked-picture-demo.xls
Featured Post
June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Suggested Courses
Course of the Month6 days, 6 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.