Conditional Formatting

Conditional Donut Chart

Wednesday, October 21, 2020 by Jon Peltier 7 Comments

Wednesday, October 21, 2020 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A recent post in the Mr Excel forum requested a conditional donut chart. The chart had to have 31 sections, one for each day of the month, and each section had to be colored according to a code in a worksheet cell. I started working on a solution, but I ran out of time. I tried returning to the post later, but I can’t find it, and Google can’t find it either.

It was a great question, though, so I’ll reproduce a simpler variation of it here.

The Conditional Donut Chart Problem

The donut chart must have eight slices, and the color of each slice must reflect a value in the worksheet.

Conditional donut chart with colors based on conditions in the worksheet.

The required color for each slice is shown in the worksheet range to the left of the chart. I have colored the cells red, yellow, and green based on whether they contain the letter “R”, “Y”, or “G”. This is easy to do in the worksheet, but there is no mechanism to change colors like this in a chart.

You could do this with VBA, of course. I’ve written a tutorial with the necessary code: VBA Conditional Formatting of Charts by Category Label. But this is a problem which does not require VBA. You can make it work just as well with a handful of clever worksheet formulas, without requiring that someone enable macros in your workbook, and without coming up with a mechanism to run the code when the data changes.

Set Up the Data

While our conditional donut chart must have eight visible slices, it will have three times that amount, and two thirds will be hidden.

I’ve reproduced the slice and color data in this screenshot. You can’t make a meaningful chart of this data, of course, so below it I have constructed an expanded data region. The Slice column repeats the slice number three times for the three colors, and the Color column repeats all three colors for each slice, not just the one which appears in the first range.

Expanding the Slice and Color data for our conditional donut chart

Insert the chart

Next I have added a column to the lower range, with a header of “Ones” and with a value of 1 in every cell. I can finally make a donut chart. With the chart selected, you can see from the data highlights in the worksheet that both the Slice and Color columns are used for category labels, and the Ones column is used for the donut slice values. The category labels are seen in the legend, and the donut slices are all the same size, 1/24 of the donut circumference.

A column of Ones was added to the data, and this was plotted in a donut chart

Format the Chart

Let’s do a little formatting. Double click on one of the donut slices to open the Format Data Series task pane, and under Series Options, change the Donut Hole Size from the default 75% to 50%. Click the plus icon that floats alongside the chart, and check Data Labels. Double click on one of the labels to open the Format Data Labels task pane, and under Label Options, check Category Name and uncheck Value. Finally, delete the legend.

Shrink the donut hole, add data labels, and remove the legend

I started formatting the slices by first making them all gray. This made it somewhat easier to see where I was in the process. To change all slices to the same color, click once on a slice to select all slices, then choose the color in any convenient way (Format Data Series task pane, ribbon control, or right-click pop-up). At the same time, keep the white border between slices, but change it from 1.5 points to 0.75.

Starting by formatting all slices to an unobtrusive color

Now, slice by slice, apply the colors. Here I have colored the slices in the first sector of the chart. To color an individual slice, click once to select all slices, then click to select a single slice, then apply the color.

Starting with the Red-Yellow-Green color scheme

Now finish coloring the slices. Here’s a trick that will work faster than clicking on and choosing its color, then clicking on the next, etc. Single click twice on the first slice to select it, then apply its red color. Then hold down Ctrl while you click the right arrow key three times, which brings you to the next red slice, and click the F4 function key. This should apply the same red color to this slice. Repeat the Ctrl+Right Arrow and F4 sequence until the red slices are formatted, then repeat for the yellow and green slices.

Properties Unfollow Chart Data Point

Before you change the chart source data, you have to apply the trick in Properties Follow Chart Data Point. This relatively unknown and totally misunderstood feature of Excel charts will otherwise screw up your chart. What it means is, if you have customized your plotted data, then change the data so the reformatted feature (chart series or data label) references a different range, some or all of the formatting will revert to its defaults.

Click on Excel’s File tab on the ribbon, choose Options, then click on Advanced. Scroll down to Chart, and you’ll see “Properties Follow Chart Data Point for All New Workbooks” and “Properties Follow Chart Data Point for Current Workbook”.

Excel Options - Properties Follow Data Points

Uncheck the box for “Properties Follow Chart Data Point for Current Workbook”.

Excel Options - Properties Don't Follow Chart Data Points

Now changing the range in the following step will not mess up the chart.

If you have Peltier Tech Charts for Excel, there is an easier-to-access checkbox on the Peltier Tech ribbon, in the Chart Format group.

Write Formulas to Apply Conditions

Now the chart is fully formatted. We just need to build the formulas that show and hide slices according to the data in the upper range.

Add two more columns to the second data range, and insert the headers “Labels” and “Values”. Ignore the Labels for now, but enter this formula into the range F13:F36:

=IF(INDEX($C$3:$C$10,B13)=C13,1,0)

The result is a column of zeros and ones, with two zeros and a single one for each slice number, according to the colors specified in C3:C10.

Click on the chart and notice the highlighted ranges in the worksheet. Drag the edge of the blue region so the highlight includes the Values column instead of the Ones column. The red highlight should move with the blue highlight if you had selected the chart or the plot area (which is the square that includes the donut). If you had selected one or all of the donut slices, the red highlight would not have followed the blue, but you can simply move it yourself.

Note that there is now only one visible slice for each of the eight sectors of the donut, and these are colored according to the values in C3:C10. Unfortunately we have a bunch of overlapping labels that we don’t want.

These labels correspond to the hidden slices, which are still in the chart. We need another formula that will let us show only the visible slice numbers. So enter this formula into the range E13:E36:

=IF(F13=1,B13,"")

Now select the pie slices (all of them or just one), drag any corner of the purple highlight so it occupies a single column, then drag the edge of the purple highlight so it includes the Labels column. The extraneous labels are gone, and we only see the slice numbers.

The Finished Conditional Donut Chart

This is our conditional donut chart. You can select and drag the lower data range off to the side of the chart; you can even cut it and paste it onto another worksheet, out of the way. Move the chart closer to the data it indirectly reflects.

When the colors for each slice are changed in the worksheet, the chart instantly reflects the change.

Change the colors in the worksheet again, and the chart updates again.

And again, and again,…

Properties Follow Chart Data Point

When I change this option as shown above, I always just uncheck the box for the current workbook. Because of a fear of missing out on something magical, I’ll admit that I always return here and check it later, and I never check the box for all new workbooks. Because not even I understand exactly what this setting does.

Highlight Min and Max Data Points in an Excel Chart

Thursday, July 9, 2015 by Jon Peltier 16 Comments

Thursday, July 9, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Here is a simple data set and an XY Scatter chart. We want to highlight the minimum and maximum points in the chart.

We could manually select the high data point and format it, then select the low point and format it. No big deal for a one-off chart. But if the data changes, we may have to manually change formatting again.

Instead we’ll add a couple columns to the data range, and use them to determine where to plot the minimum and maximum values.

The formula in cell C2 (copied and pasted in C3:C10) is

=IF(B2=MAX(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column C only if it’s the maximum value in column B. Otherwise column C contains the #N/A error value, which is not plotted with a marker in an Excel scatter or line chart.

Likewise, the formula in cell D2 (copied and pasted in D3:D10) is

=IF(B2=MIN(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column D only if it’s the minimum value in column B. Otherwise column D contains #N/A.

When we select the chart, we can see the chart data highlighted in the worksheet.

We can drag to extend the blue range to include columns C and D.

And now columns C and D are plotted in the chart. Column C (minimum) is plotted as a blue marker, and column D (maximum) as an orange marker).

Let’s do a little formatting. Right click on the Max point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Max”, and choose the bright blue text color. Format the marker so it’s an 8-point circle with a 1.5-pt matching blue border and no fill.

Right click on the Min point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Min”, and choose the gold text color. Format the marker so it’s an 8-point circle with a 1.5-pt gold border and no fill.

The nice thing about using this formula-based approach, instead of manually formatting everything, is that if the data changes, the new Min and Max are highlighted. In fact, if multiple points have the same min and max values, all will be highlighted.

This approach, of adding a series for any data you want to highlight, is very flexible. You don’t need to only highlight minimum or maximum values, you can highlight anything that you can write a formula for.

My colleague and fellow Excel MVP Leila Gharani has recorded a video on YouTube at Highlight Max & Min Values in an Excel Line Chart, which illustrates this technique. Leila has recorded many dozens of videos, which are well made and which clearly show a wide variety of Excel features and techniques.

Peltier Tech Articles About Conditional Formatting of Excel Charts

This is one example of conditional formatting in Excel charts; below are several other approaches.

Posted: Thursday, July 9th, 2015 under Formatting.
Tags: Conditional Charts, Conditional Formatting, dummy series.
Comments: 16

Create a Heat Map Using Excel’s Conditional Formatting

Monday, September 23, 2013 by Jon Peltier 5 Comments

Monday, September 23, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A reader of my post Excel 3D Charts: Charts with No Value asked how to arrange his data to display a variable on a grid, thinking I’d acquiesce to his desire for a 3D chart. My initial thought was “No way”, but I first asked what he was plotting. He was doing an agricultural experimental design with a 2×5 grid of plots planted with various plants, and wanted to see whether there was a positional variation to the results which would not have been seen in a standard ANOVA analysis. His output values typically ranged from 1000 to 5000 pounds per acre.

I thought this could be visualized in 2D without the usual 3D issues, using a heat map. Now I’ll construct such a heat map using Excel’s Conditional Formatting Feature.

I set up a 2 column by 5 row grid, and inserted random numbers between 1000 and 5000.

Original range for heat map

To represent a 2×5 grid of square plots, I adjusted the rows and columns to be the same size, in this case 50 pixels. This was an assumption on my part, but whatever the dimensions of the actual plots, you can fudge the row and column dimensions to represent these dimensions.

Expanded range for heat map

I selected this range, and clicked on Conditional Formatting on the Home tab of the Excel 2013 ribbon, and hovered over Color Scales. As far as I recall, this part of the color scales mechanism worked the same in Excel 2007 and 2010.

There are a dozen built-in color scales, and if this isn’t enough you can pick More Rules.

Conditional Formatting dropdown showing Color Scales options

Below are the five built-in options I’d be most likely to use for such a heat map, highlighted in the popup menu and displayed in theworksheet. These are a red-yellow-green 3-color diverging scale, a red-white-green 3-color diverging scale, a red-white-blue 3-color diverging scale, a white-green 2-color sequential scale, and a yellow-green 2-color sequential scale.

Color scales applied to worksheet ranges

I decided to use the red-white-green diverging scale, because I thought it would be the most color-vision-friendly of the three diverging scales.

To change to a better scale, I selected the range and selected Manage Rules at the bottom of the Conditional Formatting dropdown menu, and the Formatting Rules Manager dialog appeared.

Conditional Formatting Rules Manager

If I’d selected a larger range with multiple sets of rules, like the range shown above with five color scales applied, this dialog would show all of the rules.

Conditional Formatting Rules Manager

I selected the color scale I wanted to change, and clicked Edit Rule, to pop up the Edit Formatting Rule dialog. You can see that the options for this type of rule include 2- and 3-color scales, which are useful for heat maps, as well as data bars and icon sets, which are not.

Conditional Formatting Edit Formatting Rules Dialog

You can select three colors for minimum, midpoint, and maximum, and you can choose to define these points in several ways. Here I’ve kept the default lowest value, 50th percentile (median), and highest value, but all could be defined by percentiles, values, or formulas.

To select my colors, I went to my favorite source for color schemes at ColorBrewer2.org, by Cynthia A. Brewer at Pennsylvania State University. I decided on a color-vision safe, 9-color, purple-to-green diverging scale, and this is what ColorBrewer showed me. Click on the image to visit this selection at ColorBrewer2.org.

Click on the image to visit this selection at ColorBrewer2.org.

ColorBrewer also lets you export the colors in various ways, including as an array of RGB values:

{118, 42, 131; 153, 112, 171; 194, 165, 207; 231, 212, 232; 247, 247, 247; 217, 240, 211; 166, 219, 160; 90, 174, 97; 27, 120, 55}

I used the second (purple) and eighth (green) elements of this color scheme for the min and max colors, and kept the white central value (the ColorBrewer midpoint is about 5% gray).

Edit Formatting Rules: Purple-White-Yellow Scale

Below is the purple-white-green 3-color diverging scale (left), a purple-green 2-color sequential variation (center), and a white-green 2-color sequential variation (right). Note that these intermediate colors are not ColorBrewer’s, but are calculated by Excel.

Purple-White-Green and Purple-Green Color Scales

The three-color purple-to-white-to-green scale seems to show the variation better than the two-color purple-to-green scheme, because the latter doesn’t show much variation within the greens or within the purples. The two-color white-to-green might be the best overall choice, especially if the heat map has to be photocopied.

Posted: Monday, September 23rd, 2013 under Formatting.
Tags: Colors, Conditional Formatting, Heat Maps.
Comments: 5

Microsoft MVP Logo

Conditional Formatting of Lines in an Excel Line Chart Using VBA

Tuesday, September 11, 2012 by Jon Peltier 13 Comments

Tuesday, September 11, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Conditional Formatting of Excel Line and XY Chart Lines

Chart with conditionally formatted line segments

I’ve written a lot about conditional formatting of charts. One topic I have not covered is formatting of lines in line charts and XY charts, because this isn’t as easy to do.

Formula-Based Conditional Formatting

In Conditional Formatting of Excel Charts and Conditional XY Charts Without VBA, I showed examples of a formula-based technique to apply conditional formatting in Excel Charts. Essentially there are as many series in the chart as there are distinct formats you want to show. There is one point (i.e., one bar or marker) displayed where a point should be displayed, and the formulas control which series displays each point, with which format. Using formulas is more reliable than using VBA, because updating is dynamic without needing to run code whenever the data changes, and it’s easier to maintain. This approach can be applied to bar or column charts…

Conditionally Formatted Column Chart

… to markers in line charts…

Conditionally Formatted Line Chart

… and to markers in XY charts…

Conditionally Formatted X-Y Points

This conditional formatting is applied to each individual point, but unfortunately it cannot be applied in this way to the individual line segments connecting markers in a line chart.

VBA-Controlled Conditional Formatting

Sometimes you don’t have the luxury of using formulas for your conditional formatting. Here are a few articles showing VBA techniques for applying conditional formats to a chart:

VBA-Controlled Conditional Formatting of Line Chart Lines

You might want to format lines differently if they represent rising or falling values. Green line segments for increasing trends or red for decreasing. Actually, a more color-vision-friendly combination is blue for increasing and orange for decreasing. The chart at the top of this article is a good example.

Unfortunately, line segments in a line or XY chart cannot be turned on or off using formulas, the way markers or columns and bars can be. So we’ll have to use VBA to format them.

For this tutorial. let’s dig out some web site statistics. This chart of weekly visits show some week-to-week fluctuations, with occasional flat segments which we will not want to color.

Chart with line segments to be conditionally formatted

I decided to record a macro so I can figure out the syntax. The help files are pretty sketchy about this.

I started with a boring dummy chart with three series.

Dummy chart before recording a line formatting macro

I turned on the macro recorder while I applied the following formatting to the dummy chart. I changed line colors, and I didn’t change the marker color but shrunk the marker size

Dummy chart after recording a line formatting macro

Here’s the recorded macro. It’s no uglier than any other recorded macro.

Sub RecordedMacro1()
'
' RecordedMacro1 Macro
'

'
    ActiveChart.SeriesCollection(1).Select
    With Selection.Format.Line
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent5
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0.400000006
      .Transparency = 0
    End With
    With Selection.Format.Line
      .Visible = msoTrue
      .Weight = 2.25
    End With
    Selection.MarkerSize = 4
    ActiveChart.SeriesCollection(2).Select
    With Selection.Format.Line
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorAccent6
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = 0.400000006
      .Transparency = 0
    End With
    With Selection.Format.Line
      .Visible = msoTrue
      .Weight = 2.25
    End With
    Selection.MarkerSize = 4
    ActiveChart.SeriesCollection(3).Select
    With Selection.Format.Line
      .Visible = msoTrue
      .ForeColor.ObjectThemeColor = msoThemeColorBackground1
      .ForeColor.TintAndShade = 0
      .ForeColor.Brightness = -0.349999994
      .Transparency = 0
    End With
    With Selection.Format.Line
      .Visible = msoTrue
      .Weight = 2.25
    End With
    Selection.MarkerSize = 4
End Sub

Well, yeah, it is kind of ugly. It selects everything, then works on the selection object, and includes all that default stuff I never touched.

Long story short (long code short), I used constants for the formatting settings, streamlined the code. I also did some magic to compare whether adjacent points showed increasing or decreasing values to decide what colors to use.

Sub ColorLinesBasedOnSlope_ThemeColor()
  Dim srs As Series
  Dim iPoint As Long
  Dim vValues As Variant

  Const thmclrBlue As Long = msoThemeColorAccent5
  Const briteBlue As Double = 0.4
  Const thmclrOrange As Long = msoThemeColorAccent6
  Const briteOrange As Double = 0.4
  Const thmclrGray As Long = msoThemeColorBackground1
  Const briteGray As Double = -0.4
  Const dWeight As Double = 2.25
  Const lMarkerSize As Long = 4

  Set srs = ActiveChart.SeriesCollection(1)
  vValues = srs.Values
  For iPoint = 2 To UBound(vValues)
    With srs.Points(iPoint).Format.Line.ForeColor
      Select Case vValues(iPoint) - vValues(iPoint - 1)
        Case Is > 0
          .ObjectThemeColor = thmclrBlue
          .Brightness = briteBlue
        Case Is < 0
          .ObjectThemeColor = thmclrOrange
          .Brightness = briteOrange
        Case Else
          .ObjectThemeColor = thmclrGray
          .Brightness = briteGray
      End Select
    End With
    srs.Points(iPoint).Format.Line.Weight = dWeight
    srs.Points(iPoint).MarkerSize = lMarkerSize
  Next
End Sub

I selected my chart (actually, I was smart and selected a duplicate chart) and ran this code. Here’s what happened:

Chart with conditionally formatted line segments and ugly markers

Oof! Not only did the code format the line segments, it also formatted the marker lines. I never did that while recording the macro.

Turns out, the object model for Excel 2007/2010 charts does not distinguish between the connecting lines and the lines that draw the markers. Well, that’s no good.

Fortunately I still can use the old tired and true Excel 2003 VBA syntax. Instead of .Format.Line.Whatever, I reverted to .Border.Whatever, and I took out that weird ThemeColor drivel, and replaced it with the familiar RGB color definitions. I present the updated code.

Sub ColorLinesBasedOnSlope_RGBColor()
  Dim srs As Series
  Dim iPoint As Long
  Dim vValues As Variant

  Const rgbMyBlue As Long = 14536083
  Const rgbMyOrange As Long = 9486586
  Const rgbMyGray As Long = 10921638
  Const lWeight As Long = xlThick
  Const lMarkerSize As Long = 4

  Set srs = ActiveChart.SeriesCollection(1)
  vValues = srs.Values
  For iPoint = 2 To UBound(vValues)
    With srs.Points(iPoint).Border
      Select Case vValues(iPoint) - vValues(iPoint - 1)
        Case Is > 0
          .Color = rgbMyBlue
        Case Is < 0
          .Color = rgbMyOrange
        Case Else
          .Color = rgbMyGray
      End Select
      .Weight = lWeight
    End With
    srs.Points(iPoint).MarkerSize = lMarkerSize
  Next
End Sub

I selected another copy of the chart, and ran this new procedure.

Chart with conditionally formatted line segments and nice markers

Nice. It colored the lines orange and blue, except for a couple horizontal segments that were colored gray. It also kept the markers gray, but shrunk them so they don’t overwhelm the lines.

The markers aren’t really necessary. I like them, so I know for sure where there is actual data, but you can judge where the lines change their angle or color to know this. I made yet another copy of my original chart, removed the markers, and ran the formatting procedures.

Chart with conditionally formatted line segments and no markers

Which procedure did I use? Doesn’t matter. Both produced the same result, since there were no markers for the newfangled code to mess up.

Posted: Tuesday, September 11th, 2012 under Formatting.
Tags: Conditional Formatting, VBA.
Comments: 13

Microsoft MVP Logo

VBA Conditional Formatting of Charts by Value and Label

Thursday, March 29, 2012 by Jon Peltier 15 Comments

In Conditional Formatting of Excel Charts I showed how to simulate conditionally formatting in your charts charts. The trick is to set up ranges containing formulas for each of the conditions, so that if the condition for that range is met, the value will be plotted in the appropriately formatted chart series. Otherwise it doesn’t appear in the chart, but a differently formatted point will.

In most cases the technique in the cited article is the best approach, since it does not use VBA and it updates with the speed of Excel formulas. Sometimes, though, you need a VBA solution.

Prepare for VBA Formatting

In a worksheet named “ColorSheet”, I have set up a range that has row headers corresponding to the category labels I expect to encounter, and column headers corresponding to values I want to use as cut-offs for conditional formatting. The cell at the intersection of the category label row and value column is formatted in the way I want the chart’s data to be formatted. For example, a point with a category label of Alpha will be some shade of blue, while a point labeled Eta will be orange. The shade of the color used is lighter for smaller values and darker for larger values. I have included a label “other” in case an unexpected label is found, and a value of “above” in case the maximum in the table is exceeded. The light shades might be too light, and in any case, shades of color shouldn’t be expected to provide much resolution when encoding information.

Table of colors by value and category label

Naturally, your labels and values will be different, and in fact you may need more complicated algorithms in the code.

I’ve named this region “ColorRange”. Note that this name appears in the Name Box above column A when the range is selected.

Named range of colors by value and label

Here is my data and chart. The bars have the default formatting for series 1.

Apply Formatting by Running the VBA Code

Here is the chart after running the FormatPointByCategoryAndValue procedure.

After changing the values and rerunning the procedure, the chart’s bars keep their color, though the shades have become lighter or darker. The light green for Gamma is too light to distinguish from gray, and almost too light to see at all. I should probably go back and darken all of the lighter shades.

Same chart with modified values and reformatted bars.

Now all of the data has changed, and in fact, more data is plotted in the chart. The code still works as expected. Note the Omega series, which is colored gray because that label was not included in the original color table.

Chart with completely new data and reformatted bars

The VBA Procedure

The code is not too complicated. After the declarations (Dim and Consts) the first thing that the code does is abort if the user has not selected a chart (a little extra effort is always well-spent if it that a run time error). There is a line of code which, if uncommented, will remind the user to select a chart by showing this dialog:

Then the range containing the colors is identified and its values put into an array to make the values easier to work with.

The first series of the active chart is defined as the series we are formatting. The category labels (XValues) and values (Values) are put into arrays, also for ease of processing.

The code then looks at each point’s value and label, to determine which cell has the desired formatting. The rows and columns are looped starting at 2, since the first of each contains an irrelevant label. The looping stops one count before the end. If a match is not found, the loop counter points to the last row or column.

Finally the point is given the same fill color as the matching cell in the color table. Note that there are two sets of syntax, one for Classic Excel (2003 and earlier) and the other for Neo Excel (2007 and later). I’ve commented out the 2003 syntax, because I now use 2010 for most of my outward-facing work.

Sub FormatPointByCategoryAndValue()
  Dim rColor As Range
  Dim vColor As Variant
  Dim srsColor As Series
  Dim iRow As Long
  Dim iCol As Long
  Dim iPoint As Long
  Dim vCategories As Variant
  Dim vValues As Variant

  Const sColorSheetName As String = "ColorSheet"
  Const sColorRangeName As String = "ColorRange"

  If ActiveChart Is Nothing Then
    ' uncomment following line to alert user
    'MsgBox "Select a chart and try again.", vbExclamation, _
        "No Active Chart"
    GoTo ExitHere
  End If

  Set rColor = Worksheets(sColorSheetName).Range(sColorRangeName)
  vColor = rColor.Value

  Set srsColor = ActiveChart.SeriesCollection(1)

  With srsColor
    vCategories = .XValues
    vValues = .Values

    ' cycle through points
    For iPoint = 1 To .Points.Count
      ' find category (row)
      For iRow = LBound(vColor, 1) + 1 To UBound(vColor, 1) - 1
        If vCategories(iPoint) = vColor(iRow, 1) Then Exit For
      Next

      ' find value (column)
      For iCol = LBound(vColor, 2) + 1 To UBound(vColor, 2) - 1
        If vValues(iPoint) <= vColor(1, iCol) Then Exit For
      Next

      ' apply color of identified cell to given point
      ' Excel 2007+ syntax
      .Points(iPoint).Format.Fill.ForeColor.RGB = _
          rColor.Cells(iRow, iCol).Interior.Color

      ' Excel 2003- syntax
      '.Points(iPoint).Interior.ColorIndex = _
          rColor.Cells(iRow, iCol).Interior.ColorIndex
    Next
  End With

ExitHere:

End Sub

To run the procedure, select the chart, then press Alt+F8, select FormatPointByCategoryAndValue, and click Run.

Articles about Non-VBA Conditional Formatting of Charts

Posted: Thursday, March 29th, 2012 under VBA.
Tags: Conditional Formatting.
Comments: 15

Microsoft MVP Logo

Conditional Formatting

Conditional Donut Chart

The Conditional Donut Chart Problem

Set Up the Data

Insert the chart

Format the Chart

Properties Unfollow Chart Data Point

Write Formulas to Apply Conditions

The Finished Conditional Donut Chart

Properties Follow Chart Data Point

More Peltier Tech Articles About Conditional Formatting of Excel Charts

Highlight Min and Max Data Points in an Excel Chart

Peltier Tech Articles About Conditional Formatting of Excel Charts

Create a Heat Map Using Excel’s Conditional Formatting

Conditional Formatting of Lines in an Excel Line Chart Using VBA

Conditional Formatting of Excel Line and XY Chart Lines

Formula-Based Conditional Formatting

VBA-Controlled Conditional Formatting

VBA-Controlled Conditional Formatting of Line Chart Lines

VBA Conditional Formatting of Charts by Value and Label

Prepare for VBA Formatting

Apply Formatting by Running the VBA Code

The VBA Procedure

Related Articles about VBA Conditional Formatting of Charts

Articles about Non-VBA Conditional Formatting of Charts