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!

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?

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.

  • Chip Pearson has code for conditional formatting colors, but it doesn’t seem to work in newer versions of Excel
  • Rick Rothstein has a macro to get the conditional formatting colour (comment #5), but that didn’t work with my colour scale

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.

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.

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.

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.

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.

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

  • StackOverflow – Get Cell Color Properties
  • Jon Peltier explains pretty much everything you’d want to know about Excel colors in the user interface
  • My blog post shows how to make the Colors window bigger
  • Wikipedia – The RGB Color Model and the HSL/HSV Values

Chart Colors

  • Jon Peltier’s macro to change series color based on cell color
  • Jon Peltier’s macro to change series color based on the series name

Get the Color Workbooks

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

  • To get a copy of my Conditional Formatting Color file, go to the Excel Sample Files page on my Contextures website. In the Charts section, look for CH0011 – Chart Colour Based on Rank. The zipped file is in xlsm format, and contains macros.
  • You can download a copy of John Marshall’s Colour Lists workbook from the Technet Gallery.

________________________________

The post Excel Conditional Formatting Colour Macro Problems appeared first on Contextures Blog.

Original source: http://blog.contextures.com/archives/2018/03/15/excel-conditional-formatting-color-macro-problems/

Tags: , ,

Leave a Reply

Close Menu