Conditional formatting shapes and cells! This is a very interesting area in Excel.
In this tutorial we’ll show you how to use the conditional formatting to create live shapes using various techniques. We will show you all the tricks related to this subject. You can create smart presentations using this fantastic excel feature. We provide free downloadable spreadsheets and examples. Let’s start reading!
Formatting cells is a useful function.
Can this function be extended?
Is a shape fit to be the highlighted element of a kpi dashboard?
Of course! Stay with us today and we’ll show you how all this is possible.
Using Linked Pictures
Is may appear that conditional formatting in Excel only works with cells. But what if we would like to use formulas?
There’s no problem, let’s use the simplest and most obvious solution. This is the linked picture method.
Let’s see step by step how to create it:
First select an already formatted cell.
On the picture below we have created a little example for this. We will pay our attention to the range D5:D6.
You can see the rules in the Rules Manager window. We didn’t make it overly complicated. If the value of the cell is larger or equal to 2, than it will color it red.
If the value of the cell is smaller than 2, than it will color it green.
Copy the cells containing the Conditional Formatting as a linked picture.
We can use the Home tab on the ribbon, but it is easier to use the right-click menu.
The linked picture basically acts as a graphical object. For example we can more or resize it.
Important to note that the change of the cell formation will connote the change of the linked picture.
Write number 1 in both cells.
As an effect of the conditional formatting both cells will turn green, so the rule will prevail that we have set before.
Actually with the change of the value of the cell we can control the shape.
This was in any case the simplest solution for the use of Conditional Formatting on a shape.
Don’t forget that now we are talking about a simple picture.
Can Shapes be controlled by Conditional Formatting?
The answer is yes! And with an easy way. Some people like to overcomplicate VBA programming. There’s no need for that.
Follow the tutorial further more we’ll introduce two more methods.
Now we’d like to color a real Shape with the help of Conditional Formatting. The first thing that comes to mind is to write a short VBA code.
The colors of the shapes are very easily controlled with the help of Visual Basic.
Insert a simple object.
We would like if the color of the object would change color based on the value of the cell.
If the cell contains the word “RED” than the VBA code will color the circle to red.
The same principal will be true for the “YELLOW” and “GREEN” variants also.
We start from the desire that we don’t want manually run any code or assign it to buttons.
Smart to start the code with the event of Private Sub Worksheet_Change (ByVal Target As Range).
This event ensures that if anything changes on the given worksheet a little VBA program will run.
Explanation for the VBA codes:
If the value of C8 cell = “RED”, than that will highlight the shape called Shape1 that can be found on the current / active worksheet, than changes its color to red.
On other cases (for example if the C8 cell doesn’t contain the word “RED”) will do the following.
If the text is = “YELLOW” will color the object yellow.
And if the C8 cell contains the word “GREEN”, than it will color it green.
We are done with this.
It’s not very hard to create this code, isn’t it?
We don’t need at all advanced knowledge of VBA.
We can find out in a matter of moments the name and the changed color code with macro recording.
Apply an Automated VBA Solution
Finally we introduce a solution we recommend everyone who is creating excel dashboard using conditional formatting.
VBA developing is not always a grateful thing, but it’s time to proudly announce the good news. This macro is little but a very well applicable solution.
This small excel file finally bridge over the sometimes extremely frustrating limitations of Excel.
With the help of conditional formatting shapes method we again will push the limits of Excel.
Before we get into the middle of it, we’ll show the end result.
As you can see on the picture above the color of the shape depends on the given value in the cell. It will change dynamically depending on the size of the values.
Now in brief we demonstrate how to use the template:
1. Insert a shape on any given worksheet.
2. We choose the oval object:
3. In the Name Box change the default name to any given name:
Why is this important? We store on the setup sheet those values and colors that make the object dynamic. We give every object a specific name this way we can easily identify them.
4. Let’s take a look at the setup worksheet!
The meaning of the columns:
Column A: this is an ID, don’t carry any importance, we can write anything here.
Column B: the name of the object, this must be the same that we written in the Name Box a minute ago.
The Upper Limit is the number above which the color of the shape turns to green (Color1).
The Lower Limit is the number below which the shape turns to red (Color3).
Between the upper and lower limits the color of the shape will be yellow (Color2).
5. The colors can be arbitrarily changed.
6. Actual Value: doesn’t need any explanation, it is the current value.
7. Let’s go back to Sheet1. highlight the object and in the Formula Bar field start to type in the following:
This will set the current value to 45. Based on the table the shape will change its color to yellow because this value is between the upper and the lower value range.
From now on we can freely insert newer rows and you can command more shapes automatically any given time.
Conditional Formatting shapes: Improve your Excel skills
As conclusion we can say that conditional formatting shapes is capable of doing way more things than most of us think. On the sample workbook you can find the VBA code which you can freely use and modify.
There are many possibilities in Excel, we’ll try to introduce as many of them as possible. See you next week! The macro-enabled worksheet can be downloaded from here!