Excel Conditional Formatting Colour Macro Problems

If you need to know what color is in a worksheet cell, or a shape, you can check it manually, or use VBA code to find the color number. There are conditional formatting color macros too, but they’re a bigger challenge, and didn’t work for a project that I built recently. Here’s what I was working on, and some of the Excel color resources that I used along the way. There are links to download my sample file, and one from Visio MVP, John Marshall.

Conditional Formatting Color Chart

Someone asked if it was possible to use conditional formatting in an Excel pie chart. They had found formulas to add conditional formatting in a column chart, but that didn’t do what they needed. In this case, the data had two different amounts – the total orders, and the total amount that had been invoiced. From those amounts, the Percent Invoiced had been calculated. The pie chart needed to show

  • slice size based on the order amount
  • slice colour based on a colour scale for the % invoiced

That sounded like an interesting challenge, so I decided to tackle it.

Sample Data

  • To investigate the problem, I created a small data set, similar to what the email had described. The first column had client names
  • Second column showed total order amount
  • Third column had the total amount invoiced
  • Final column calculated the percent invoiced
  • There was color scale (Green-Yellow-Red) on the % Invoiced column

Here’s a screen shot of my sample. It has 11 Client names – the same number that the email question had used.

  • The highest % Invoiced cell is for Ann – it’s dark green, because that’s a good value.
  • The lowest % Invoiced is for Hal, and it’s dark red, because that’s a bad thing!

colourlists05

Chart Colours

Here’s what the completed chart should look like.

  • Ann and Bob have large slices, and they’re both colored green, so their invoicing percentage is good.
  • Hal and Ida have small slices, but they’re colored red, so the invoicing has fallen behind for those clients.
  • Fran has one of the largest slices, and it’s colored orange, so that account should be checked soon!

But, how can we get those colors into the chart?

colourlists06

Get the Color Scale Colors

If you manually check the color for one of those % Invoiced cells, it shows “No Fill”, because the color comes from conditional formatting. A Google search found lots of macros that returned the fill color, but only a few that promised to return a conditional formatting color. And none of those worked for me.

Update: Thanks to Andy Pope, who gave me the property that I needed to get the Conditional Formatting color — DisplayFormat. I’ve changed the macro to use that property.

My Clunky Workaround

Because I couldn’t get the conditional formatting colors (before Andy Pope’s tip), I used a clunky workaround. It involved a second sheet with a color list, with the cells filled with colors from the color scale. The painful steps are described below, but you can skip reading this, and go straight to the macro.

Get the Color Scale

The sample data has 11 clients listed, so the chart needed to match a color scale of that size.

  • First, I added a ranking column in the sample data, to calculate the rank for each % Invoiced amount.
  • Next, I added a Red – Yellow – Green color scale formatting to the Rank column cells.

As you can see, the colors are slightly different, because the percentages aren’t equally distributed, like the numbers 1 to 11 are. So, I removed the conditional formatting from the % Invoiced column, and decided to use the colours for 1 to 11.

colourlists07

The Color List

Next, I created a Color List

  • On a “Colours” sheet in the workbook, I typed a list of numbers, from 1 to 11.
  • Then, I applied a Red – Yellow – Green conditional formatting color scale to those cells.
  • That list was named, as ColorList
  • Next, I needed to find out what those colors were, so I could use then in the chart.

And that’s when the fun began.

colourlists03

The Color Workaround

Since I couldn’t get the conditional formatting color with a macro, I had to resort to brute force, to get the color for each ranking number. Close your eyes, if you’re a squeamish type – this gets a bit ugly!

  • To get the colors, I copied the ColorList cells.
  • Then, I pasted them into Word
  • Next, I copied the list from Word, and pasted them back onto the 1-11 cells in Excel
  • The conditional formatting was gone, and the cells had a fill color instead.

The colors were the same, but now Excel could extract the color number from those cells The macro would find the cell in the color list that matched the client’s rank number, and use the color from that color list cell. However, now that the revised macro uses the DisplayFormat property, the workaround isn’t needed. The macro can get the conditional format color from the % Invoiced column instead.

Macro to Color the Chart

The original request was for a pie chart with conditional formatting. In the sample file, I also created a Bar Chart, because it’s easier to compare the client amounts in that type of chart, instead of a pie chart. Here’s the macro – ColorChartDataPoints – that I use to color the pie chart slices, and the bars in the Bar Chart, based on the % Invoiced conditional formatting for each client.

  • The macro gets the location of the source data, based on the formula for the chart series.
  • Then, it gets the conditional formatting color from the 3rd column to the right of the source data’s start column
  • That color is used for the slice/bar.

NOTE: The macro has been updated to use the DisplayFormat property, instead of a ranking list.

Sub ColorChartDataPoints()
'colour data point based on
'value in rank column
Dim ws As Worksheet
Dim ch As ChartObject
Dim ser As Series
Dim dp As Point
Dim ptnum As Long
Dim rngSD01 As Range
Dim strF  As String
Dim strRng  As String
Dim CharStart As Long
Dim CharEnd As Long
Dim ColOff As Long
Dim PtColor As Long

ColOff = 3  'offset to Rank column
Set ws = ActiveSheet

For Each ch In ws.ChartObjects
  Set ser = ch.Chart.FullSeriesCollection(1)
  strF = ch.Chart.SeriesCollection(1).Formula
   
  CharStart = InStr(1, strF, ",")
  CharEnd = InStr(InStr(1, strF, ",") _
        + 1, strF, ",")
  strRng = Mid(strF, CharStart + 1, _
         CharEnd - CharStart - 1)
  Set rngSD01 = ws.Range(strRng)
  ptnum = 1
  
  For Each dp In ser.Points
    PtColor = rngSD01.Cells(ptnum, 1) _
        .Offset(0, ColOff).DisplayFormat.Interior.Color
    dp.Format.Fill.ForeColor.RGB = PtColor
    ptnum = ptnum + 1
  Next dp
Next ch

End Sub

The Final Result

Here’s the finished Conditional Formatting Color report, with the sample data, the bar chart, and the pie chart. It was an interesting project, so download the workbook (link below), if you’d like to test it.

NOTE: There is also an event procedure on the Chart sheet, and it automatically runs the ColorChartDataPoints macro, if an Order total or Invoice amount is changed.

colourlists08

Color Lists Workbook

All this work with Excel colors reminded me that John Marshall had sent me his Excel Colours project a while ago. If you’d like an Excel file with tons of colour lists, you can download a copy of John’s sample file from the Technet Gallery.

John is a Microsoft Visio MVP, and needed colour lists for a Visio project. That’s easier to do in Excel, so he set up the colour lists there. The workbook has 11 sheets with colour samples, and the RGB, Hex, and other values. The main list is shown below

If you select a cell, and go into the More Colors option, you can see the RGB settings for the selected cell, on the Custom tab. Those numbers should match the values that you’ll see in the Colour lists of John’s workbook.

colourlists02

More Color Lists

There are several other colour lists in John’s workbook too, including Crayola, LEGO and Pantone. On the workbook’s Introduction sheet, John gives details on the source for each list.

To keep the workbook from slowing down, the colour list formulas are in the first row only (orange cells). Below that, the formulas have been copied, and pasted as values (green cells). If you change any of the colours, you could copy the formulas down again.

Most of the formulas are based on User Defined Functions (UDFs) that calculate each colour’s values.

In the screen shot shown below, you can see a formula that uses a UDF named RBG_to_HSV UDF. It calculates the colour’s Hue, based on the RGB (Red Green Blue) values in columns B, C and D.

The workbook and its code are unlocked, so go to the Visual Basic Editor, and take a look at the functions in the code module, to see how they work.

colourlists01

More Excel Color Resources

Here are a few more of the Excel Color, and Excel Chart Color resources that I used, while working on this interesting project.

Color Info

Chart Colors

Get the Color Workbooks

Here’s how to get the two sample Excel files that I mentioned:

________________________________

5 thoughts on “Excel Conditional Formatting Colour Macro Problems”

  1. Hi Debra,
    In more recent versions of xl, 2013 and later IIRC, you can read the DisplayFormat information of a cells to get the result of conditional formatting.

    So a quick tweak to you code example will pick the colours directly from Rank column with CF applied.

    PtColor = rngSD01.Cells(ptnum, 1) _
    .Offset(0, ColOff).DisplayFormat.Interior.Color

  2. Thanks, Andy! I’ve updated the article and the macro, to use that property. I vaguely remember trying DisplayFormat, but maybe it was in a UDF, instead of the macro. This makes this much simpler!

  3. You don’t need to use a third index column for your colors: you can use the conditionally formatted cells themselves. This way, ties will have the same color (compare 70 and 71% in your original column, which are nearly the same, with the ranks 7 and 8, which are visibly quite distinct).

    Check out this code:

    Sub ColorChartDataPoints()
    ‘color data point based on
    ‘conditional formatting color scale
    Dim ch As ChartObject
    Dim srs As Series
    Dim PointIndex As Long
    Dim SrsFmla As String
    Dim vFmla As Variant
    Dim YValuesAddress As String
    Dim YValuesRange As Range
    Dim PointColor As Long

    For Each ch In ActiveSheet.ChartObjects
    Set srs = ch.Chart.FullSeriesCollection(1)
    SrsFmla = ch.Chart.SeriesCollection(1).Formula

    vFmla = Split(SrsFmla, “,”)
    YValuesAddress = vFmla(LBound(vFmla) + 2)
    Set YValuesRange = Range(YValuesAddress)

    For PointIndex = 1 To srs.Points.Count
    PointColor = YValuesRange.Cells(PointIndex).DisplayFormat.Interior.Color
    srs.Points(PointIndex).Format.Fill.ForeColor.RGB = PointColor
    Next
    Next ch

    End Sub

    1. Thanks, Jon, and after Andy Pope commented last March, I changed my macro to use that conditional formatting, without needing a Rank column.
      There’s a note, just above my macro code,
      I’ve just changed it to Bold text, so it will stand out more.

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.