How to change shape color based on cell value in Excel?
Change the shape color based on a specific cell value may be an interesting task in Excel, for example, if the cell value in A1 is less than 100, the shape color is red, if A1 is greater than 100 and less than 200, the shape color is yellow, and when A1 is greater than 200, the shape color is green as following screenshot shown. To change the color of the shape based on a cell value, this article will introduce method for you.
The below VBA code can help you to change the shape color based on a cell value, please do as follows:
1. Right click the sheet tab which you want to change the shape color, and then select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window.
VBA code: Change shape color based on cell value:
Private Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice 20160704 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) Then If Target.Value < 100 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed ElseIf Target.Value >= 100 And Target.Value < 200 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow Else ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen End If End If End Sub
2. And then when you enter the value in cell A1, the shape color will be changed with the cell value as you defined.
Note: In the above code, A1 is the cell value your shape color would be changed based on, and the Oval 1 is the shape name of your inserted shape, you can change them to your need.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agoHi... excellent solution... but how do I apply it to multiple shapes based on the corresponding values of a range of cells. Many thanks in advance for your help.
- To post as a guest, your comment is unpublished.· 1 years agoHow do I make the private sub to read the result from the AVERAGE(C1,C5,C9) calculation?
Sub only works with numeric values; any thoughts and suggestions are greatly appreciated.
- To post as a guest, your comment is unpublished.· 2 years agoThanks for this which is really useful.
I now want to use it with a pivot table on another worksheet which controls the data on the sheet with the shapes that I want to change colour. However, when I change the selection on the pivot table the data on the worksheet with the shapes is updated but the code does not run so the shapes do not change colour
If I manually change the values the code runs and the colour of the shapes is updated.
Question: what do i need to add to the code above to allow it to run automatically?
- To post as a guest, your comment is unpublished.· 2 years agoHow can this be applied if you have multiple shape in the same worksheet?
- To post as a guest, your comment is unpublished.· 2 years agoGreat vba solution.
It is possible to also use conditional formatting to colour the shapes.
Set the name of each shape as the cell value. Using a With Each Shape then set the shape colour as the cell colour for all named shapes.
The cell colour may be changed using conditional formatting based on numerical values.
For example the colour of a semi transparent overlap on a city map can be used to graphically indicate population density per block with a graduated colour scheme.
- To post as a guest, your comment is unpublished.· 2 years agoCan you share an example of the code?
- To post as a guest, your comment is unpublished.· 2 years agoI have 300 shapes in a sheet. Is it possible to check the adjacent or linked cell's value (empty or non-empty) in a sheet and color the linked shapes through VBA code?
- To post as a guest, your comment is unpublished.· 3 years agoHow about if we have more than 1 object in the worksheet which the colors change according to the value input say in A1, B1,C1....