In the same way, we will use formulas to define the formatting of series in the charts. Except for some simple built-in formats, conditional formatting of worksheet ranges requires formulas to determine which cells should take on the formatting. We want our charts to show different colored points depending on the points’ values. The data makes a simple unformatted bar chart. Here is the simple data for our conditional chart formatting example. The following technique works very well without resorting to macros, with the added advantage that you don’t have to muck about in VBA. This can be done using VBA to change the individual chart elements (for example, VBA Conditional Formatting of Charts by Value), but the code must be run whenever the data changes to maintain the formatting. People often ask how to conditionally format a chart, that is, how to change the formatting of a chart’s plotted points (markers, bar fill color, etc.) based on the values of the points.
Conditional formatting of charts is a different story. It’s relatively easy to apply conditional formatting in an Excel worksheet. It’s a built-in feature on the Home tab of the Excel ribbon, and there many resources on the web to get help (see for example what Debra Dalgleish and Chip Pearson have to say).
