Conditional color format data markers

**ArtZ** · 07-14-2008, 06:38 PM

Hi,

Sounds like one for Andy. Which is why I've moved it.

Richard Buttrey (Mod)
-----------------------------------------

Hi,

I have a line chart with data points plotted around two reference lines. Data values are less than equal to, or somewhere in between the upper and lower reference lines.

Using VBA, is it possible to format the data point markers that are greater than or equal to the upper reference line red and to format the data point markers that are less than or equal to the lower reference line green?

The data are dynamic and their position on the chart varies as new data are entered, so doing this manually is not a good option.

Does anyone in the Forum have a suggestion on how to do this?

Thanks,

Art

**Andy Pope** · 07-15-2008, 03:52 AM

Charts do not support conditional formatting.
You can however create the effect by using multiple series.

These new series will display points that are above or below certain ranges by using formula.

Post a example workbook if you need more help.

**ArtZ** · 07-15-2008, 05:24 PM

Andy,

Thanks for your offer. Attached is my worksheet. Please let me know if you have any questions.

Thanks,

Art

**Andy Pope** · 07-16-2008, 04:29 AM

See attached.

**ArtZ** · 07-16-2008, 11:46 AM

Andy,

Thanks for your help. Much appreciated. One problem that I should have anticipated: if the reg length value in cell Q2 is increased from the current value (it's a user input) and new data are downloaded, the conditional formatting is present only for the first occurrences.

I tried extending your calculation down to accommodate a longer data length and changed the chart data to include the longer length. This sort of worked, however, it messed up the x scaling on the chart.

Can the conditional formatting be modified so that it works for any data length?

Thanks,

Art