Learn how to create conditional formatting shapes in Excel! In this tutorial, we will explain how to create interactive charts using conditional formatting. Believe it or not, it’s possible to apply colors on chart objects using some VBA tricks! You will learn how to extend the formatting cells function and step on the next level in Excel.
Here is a grid layout example below. We love it! If you read the article, you’ll able to create advanced report elements and various scorecards in Excel.
In the next section, we’ll introduce the manual way. Preparing the code a little bit time-consuming task, but we’ll help you!
Create Dynamic Shapes using conditional formatting
Dynamic shapes play an important role and illustrate your data in Excel graphically. It’s a great addition to any excel dashboard.
Worksheet Events – Using triggers in Excel
What is the Worksheet Event? Excel can automatically run any macro when data in any cell on Worksheet changes. We can use it to run our code only when specific cells change. We’ll use this method to created dynamic widgets based on a cell’s value.
Our goal is to create formatted color shapes based on cell value. We are trying to conditionally format shapes in Excel to red, yellow or green based on a cell instruction stating ‘red,’ ‘yellow’ or ‘green.’ We’ll give you a complete solution using simple VBA macros.
Steps to create conditional logic for shapes
1. Open the VBA editor. Because we don’t want manually to run any code Select Sheet1 and place the code below:
Private Sub Worksheet_Change(ByVal Target As Range) If Range("B3") = "red" Then ActiveSheet.Shapes.Range(Array("Shape1")).Select Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0) Else If Range("B3") = "yellow" Then ActiveSheet.Shapes.Range(Array("Shape1")).Select Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent4 Else If Range("B3") = "green" Then ActiveSheet.Shapes.Range(Array("Shape1")).Select Selection.ShapeRange.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6 End If End If End If ActiveSheet.Cells(3, 2).Select End Sub
Take a closer look at the macro!
We’ll test more than one condition. In this example, it’s necessary to use multiple IF statements together in one subroutine.
If the value (text) on the given cell = “red,” then the code applies a red fill on the Shape1. If the cell is empty or the text not equal as “red,” the macro jump into the next case.
To build up a macro that reflects this logic, we can start by testing to see if the cell value is equal with ‘Yellow.’ If the result is TRUE, we shade the vector object as yellow. If the condition is FALSE, we move forward and check the result of the third IF function.
Finally, we test to see if the cell value is equal to “green.” If TRUE, we return the object, and it will be green. If FALSE, we exit the subroutine.
Now save the Workbook as an xlsm format.
2. Create a new sheet and insert a new object. On the ribbon, select the Insert menu, then choose Shapes.
3. Rename the default name to Shape1.
4. Let’s check the macro! Enter the text “green” in the cell B3. Now the macro will apply a green color.
Work without limits in Excel: Dynamic shapes
When using Excel to create reports, there is a limitation to what you can do using only the cells available to you. How to transform our presentation to create interactive, value, and color based widgets?
In this section, I reveal my best tricks to build interactive dynamic shapes that contain conditional formatting rules. You can learn something unique about this topic.
Check the result before we get into the middle of the guide: The shapes colors depend on the cell value, and it will change dynamically! Let’s see the explanation!
- As first, choose an object from the list and insert a new object.
- Use the Name Box to change the name from the default to “Shape1”.
- It’s important to use specific names for every object to identify the vector objects easily.
- Check the first row on the setup Worksheet; now we’ll explain how this parameter table work:
The shape will be:
- green, if the actual value is greater than the upper limit,
- yellow, if the actual value is between the upper and lower limit,
- red, if the actual value is less than the lower limit.
Color1, Color2, and Color3 represent the color scale. Change the color scheme if you prefer a custom color theme.
Link the object to the value
If you want to link the object to a value in a cell, make sure the shape is selected from its edges.
Explanation: If the cursor is not blinking inside the shape, you assign the value. Click the formula bar, press “=” and select the cell that has the value you want to point to. In this case, cell H2.
The cell value will be assigned to the given object. In this case, the current value is 70. The value is between the upper and lower range, so the shape will be yellow.
Format the value, increase the font size, and align the text. Because we are using triggers, you can modify the actual value; the dynamic shape will reflect on the changes.
Need more widgets to create a stunning report? No problem! Insert a new row and fill the necessary information for the new vector object. Build great-looking chart widgets in minutes!
Conclusion: Pimp your Dashboard using VBA
It’s a smart idea to use conditional formatting and shapes for stylish Excel reports. Use our free templates to show how your KPIs are performing based on your targets. VBA is a perfect tool to develop suitable solutions in Excel.