Change Excel Shape Colour Automatically

Written by Debra Dalgleish from Contextures Blog

We all want to be remembered for our contributions to humanity. That’s why, way back in 2004, I made an Excel Smiley Face sample (aka “Happy Face”). There was a new version in 2009, and here’s another update, to change the Smiley Face shape colour automatically. You can do the same thing with other shapes too, like partial circle “pie slice”. Is there a Nobel Prize in this category?

Original Excel Smiley Face

I don’t remember what inspired my first Excel Smiley Face sample file. Maybe someone asked a question in the old Excel newsgroups, and that was my answer.

Anyway, the worksheet has a Smiley Face shape, and a cell where you can enter a whole number between 1 and 100.

smileyface02

Number Cell Data Validation

In cell H3, where the number is entered, there is a data validation rule that controls what can be typed in the cell.

As you can see in the screen shot below, you’re only allowed to enter a Whole number, and it must be between the Minimum of zero and the Maximum of 100.

smileyface03

Smiley Face Mouth Curve

If you click on the Smiley Face shape to select it, you’ll see handles on the shape’s border.

There’s also a yellow handle on the mouth, and you can drag that up or down, to change the mouth’s curve.

smileyface04

Record a Macro

If you record a macro while you select the shape and then drag that mouth handle, you’ll get code similar to this screen shot.

smileyface05

Change the Curve Automatically

In the Happy Face sample file, there is code similar to that, and it runs automatically when you type a number in cell H3.

There are 2 versions of the Happy Face code in the sample file, one for Excel 2003, and one for Excel 2007 and later.

I had to create that new code because the angle settings change in Excel 2007, but I’m not sure why. You can read more about that change to the code in the Smiley Face Chart Update article.

See the Mouth Curve Code

To see the Excel 2007 code, right-click Happy Face Gauge 2007 sheet tab, and click View Code.

There is a Worksheet_Change procedure, and it changes the mouth’s curve if the worksheet change was made in cell H3.

If Target.Address = "$H$3" Then
    Application.EnableEvents = False
    sh.Adjustments.Item(1) _
      = myMin + (myMax - myMin) _
      * Target.Value / 100
End If

Change the Face Colour

Recently, someone asked if it was possible to change the face colour too, along with the mouth curve.

Well, that sounded like fun, so I’ve made a new version of the Excel Smiley Face, and it also changes colour, based on the number in H3.

smileyface06

There are numbers on the worksheet, to explain when the colour changes, but that list is for reference only.

Revised Worksheet_Change Code

To make the colour change, I added a new section in the Worksheet_Change code.

There’s a new variable, myColor, and it’s based on the number in H3. The 90 and 60 are typed in that code, so you could change them there, if you want to set different limits.

'change shape colour
'less than 60% = red
'60%- 90% Amber
'90%-100% Green
  Select Case Target.Value
    Case Is >= 90: myColor _
      = RGB(146, 208, 80)  'green
    Case Is >= 60: myColor _
      = RGB(255, 192, 0) 'orange
    Case Else: myColor _
      = RGB(255, 0, 0) 'red
  End Select

  sh.Fill.ForeColor.RGB = myColor

So, if the number in H3 is less than 60, the face changes to red.

smileyface09

Shape RGB Setting

To set the colour for the shape, I had to get the RGB settings for each colour:

Green: RGB(146, 208, 80)
Orange: RGB(255, 192, 0)
Red: RGB(255, 0, 0)

If you’d like to use other colours, here’s how you can find their RGB settings:

Right-click a cell, and click the arrow beside the Fill Color button
Click More Colors
Click a color on the Standard tab, then click the Custom tab
For Color Model, choose RGB
Make note of the Red, Green and Blue numbers
Click Cancel, to close the window

smileyface08

Then, edit the Worksheet_Change code, to use your new RGB settings.

Other Types of Shapes

You could use the same technique to change the fill colour for different types of shapes – maybe your co-workers aren’t the “Happy Face” type!

There’s another sheet in the new Happy Face file, and it has a “Partial Circle” shape. Change the percentage in cell H3, and the “pie slice” changes size, and the colour changes too.

There are 4 colours for this shape, and those are shown on the worksheet.

smileyface10

Download the Sample Files

To see how the Smiley Faces work, you can download the workbooks from the Excel Sample Files page on my Contextures website.

The new Happy Face file is in the VBA section – UF0050 – Happy Face Gauge Colour

The original Happy Face file is in the Data Validation section – DV0018 – Happy Face Gauge.

_____________________