Skip to main content

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.

doc change shape color 1

Change shape color based on cell value with VBA code

arrow blue right bubble Change shape color based on cell value with VBA code

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

doc change shape color 2

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.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (21)
Rated 4 out of 5 · 1 ratings
Emilly
Emilly
This comment was minimized by the moderator on the site
Como faço para para variar as cores da forma se minha opções for em formato de texto, como "Sim" e "Não"?
Reply
0
0
skyyang
skyyang     Emilly
This comment was minimized by the moderator on the site
Hello, Emilly
To solve your problem, please apply the below code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "Yes" Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed
Else
If Range("A1") = "No" Then
ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen
End If
End If
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
Neil
Neil     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

I tried the original VBA code and got it to work, although it would not activate actively when the cell changed. Today the code is not working and I also tried your more simplistic code and still did not work. The only thing that changed is that I copied the worksheet which contained the code that worked. Would this then result in it not working?
Reply
0
0
Roberto tovar
Roberto tovar
This comment was minimized by the moderator on the site
Hola, excelente ejemplo.
Pero como seria si tengo una forma y quiero ir coloreado poco a poco dependiendo del valor ejemplo:
Si el valor es 50%
Seia mitad roja y mitad verde
Pero que se vaya llenando según el porcentaje vaya aumentando
Reply
0
0
Rated 4 out of 5
Max
Max
This comment was minimized by the moderator on the site
I am new to VBAs and am struggling with something. I need to have 9 different cells A1-A9 change the colour of 9 different objects. Objects are cubes 1-9. Just to clarify, each cell is to change just one object A1-Cube 1, etc. Red if it fails to meet the value and green if it exceeds the value. The pass/fail value can change so instead of having the value in the VBA I need it to reference cell A10 which has the pass/fail value. Any chance someone could through out a sample code for me to work with.

Thanks
Reply
0
0
mnsosa
mnsosa
This comment was minimized by the moderator on the site
Great solution! How can I do if I have more than 1 oval in the worksheet which the colors change according to the value input say in A1, B1,C1? Thanks in advance for your reply! 
Reply
Report
1
0
MandyZhou
MandyZhou     mnsosa
This comment was minimized by the moderator on the site
Hello mnsosa,Glad to help. Please copy and paste the below VBA code into the blank Module window.
Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.Please see my screenshot. Hope it can help. Have a nice day.Sincerely,Mandy
View Attachments (1 / 5)
Reply
Report
0
0
María Noel
María Noel
This comment was minimized by the moderator on the site
¿Cómo hacemos si tenemos más de 1 Oval en la hoja de trabajo cuyos colores cambian de acuerdo con el valor ingresado, por ejemplo, en A1, B1, C1...? Mil gracias por su ayuda!

Reply
0
0
MandyZhou
MandyZhou     María Noel
This comment was minimized by the moderator on the site
Hello María Noel,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Ryan
Ryan
This comment was minimized by the moderator on the site
Hi... 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.
Reply
Report
0
0
MandyZhou
MandyZhou     Ryan
This comment was minimized by the moderator on the site
Hello Ryan,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Cesare
Cesare
This comment was minimized by the moderator on the site
How 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.
Reply
0
0
MandyZhou
MandyZhou     Cesare
This comment was minimized by the moderator on the site
Hello Cesare,How are you? I notice that the VBA code can work with the AVERAGE(number, number...) calculation. But the trick is that every time you change the values in the calculation, you need to double-click the formula in the cell to make the VBA work again. 
For example, In cell A1, after we input the formula is =AVERAGE(C2:D3), the VBA works and changes the color of the shape accordingly. Please see screenshot 1. <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">C2:D3, the returned result in cell A1 changes, but the shape color hasn't been changed yet. In this case, we need to double click the formula in cell A1 to make the VBA work. Then the shape color will be changed accordingly. Please see screenshots 2 and 3.</span>
View Attachments (2 / 5)
Reply
Report
0
0
Steve A
Steve A
This comment was minimized by the moderator on the site
Thanks 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?
Reply
0
0
Yasir
Yasir
This comment was minimized by the moderator on the site
How can this be applied if you have multiple shape in the same worksheet?
Reply
0
0
MandyZhou
MandyZhou     Yasir
This comment was minimized by the moderator on the site
Hello Yasir,
Glad to help. Please copy and paste the below VBA code into the blank Module window.

Sub TestMacro2()
Dim dblHt As Double
Dim rngC As Range
Dim lngr As Long
Dim dblMargin As Double
Dim lngSR As Long

lngSR = 2 'Row where the data starts

dblMargin = 6 ' Distance between shapes

'On Error Resume Next
ActiveSheet.Shapes.SelectAll
Selection.Delete
On Error GoTo 0


dblHt = Rows(lngSR).Height * 4

For lngr = lngSR To Cells(lngSR, "A").End(xlDown).Row
ActiveSheet.Shapes.AddShape(msoShapeOval, _
Cells(lngSR, "D").Left + ((lngr - lngSR) Mod 4) * dblHt + dblMargin, _
Cells(lngSR, "D").Top + Int((lngr - lngSR) / 4) * dblHt + dblMargin, _
dblHt - 2 * dblMargin, _
dblHt - 2 * dblMargin).Select
Selection.Name = "Round" & Cells(lngr, "A").Address
Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Cells(lngr, "A").Value
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
.Bold = msoTrue
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 12
End With
With Selection.ShapeRange.Fill
.Visible = msoTrue
If Cells(lngr, "A").Value > 70 Then
.ForeColor.RGB = RGB(0, 176, 80)
ElseIf Cells(lngr, "A").Value >= 40 Then
.ForeColor.RGB = RGB(255, 255, 70)
Else
.ForeColor.RGB = RGB(255, 0, 0)
End If
.Transparency = 0
.Solid
End With
Next lngr
Range("A1").Select
End Sub

After you run the VBA code above, you will see multiple shapes are generated, and the colors of these shapes are changed according to the VBA.
Please see my screenshot. Hope it can help. Have a nice day.
Sincerely,
Mandy
View Attachments (1 / 5)
Reply
Report
0
0
Alan
Alan
This comment was minimized by the moderator on the site
Great 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.
Reply
0
0
Ed
Ed     Alan
This comment was minimized by the moderator on the site
Can you share an example of the code?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations