Change the Color of Shape in Excel Based on Cell Value using VBA

Change the Color of Shape in Excel Based on Cell Value using VBA

You can insert different kinds of shapes in your Excel worksheet, like a circle or a rectangle etc. These shapes can be used for various purposes and you can easily change its properties like the color or the size etc. You can also dynamically change the color of a shape based on values in specific cells using VBA.

Change the Color of Shapes based on Cell Values in Excel using VBA

Let us assume I have a rectangle shape on my worksheet and I want my macro to change the color of the shape based on certain values entered in the cells. I have a single column named status, which has three rows.

When I enter the value shipped in the second row (A2), the shape’s color would turn yellow.
When I enter the value delivered in the third row (A3), the color would turn green.
When I enter the value on hold in the fourth row (A4), the color would turn red.
When there are no values in either the cells in all the 3 rows, the shape’s color would be white.

Here’s how you can do it.

Open an Excel file and insert a rectangle shape on the sheet. You can set the default color as white.

Add a Rectangle Shape in Excel and Change Color using VBA

Related Post: How to Add Text to Shapes Dynamically in Excel using VBA

Click Alt+F11 to open the VBA editor. From the Project Explorer double click the Sheet where you have the data. You can also right click the sheet tab and choose View Code.

Write the code inside Worksheet_Change event. Since, I want to change the shape’s color instantly when the value in any cell changes.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbWhite

    If UCase(ActiveSheet.Cells(2, 1)) = "SHIPPED" Then
        ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbYellow
    End If
    
    If UCase(ActiveSheet.Cells(3, 1)) = "DELIVERED" Then
        ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbGreen
    End If
    
    If UCase(ActiveSheet.Cells(4, 1)) = "ON HOLD" Then
        ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbRed
    End If

End Sub

The color of the shape changes according to the values, which I have explained. Now, see this line in the code …

ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbWhite

The function Shapes() takes a parameter, that is, the name of the shape. You can get the name of the shape by clicking on the shape and see its name in the Name Box.

Get the Name of a Shape in Excel

Change the name of the shape according to your requirement.

Similarly, you can add different shapes like a circle or an arrow, get its name, provide the name to the function and change the shape’s color using the code.

You may also like: How to change the column width and row height dynamically in Excel using VBA

Also, do this: You can change the color of multiple shapes using the example I have shown above. Simply add more shapes to your sheet and provide the names of each shape to the function. Like this …

' FOR RECTANGLE OR SQUARE SHAPE.
ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor.RGB = vbRed

OR

ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbBlue   ' FOR CIRCLE SHAPE.

Well, that’s it. Thanks for reading.

Previous - How to Parse Outlook Emails and Show in Excel Worksheet using VBANext - How to Add Text to Shapes Dynamically in Excel using VBA



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!