Format a chart color based on a cell

[tlindeman]

Could you please supply me the VBA to format a column on a bar chart based on the color of the cell. So if cell A1 is blue I would like point 3 on the chart to be blue.

Thank You
Tony

 

[Sthrncali]

Could you please supply me the VBA to format a column on a bar chart based on the color of the cell. So if cell A1 is blue I would like point 3 on the chart to be blue.

Thank You
Tony

Will it always be cell A1 and Point 3 that you are trying to match the color on? Or are you trying to match the color of every point in the chart with its source cell?

 

[tlindeman]

Every point. I have conditional formating in the cell, so I would like to match that. The colors will be either blue or orange. What I am trying to do is if the point is less than today, blue, otherwise orange.

Thank You
Tony

 

[Sthrncali]

Ok well conditional formatting is a little more difficult to match in VBA because there is no actual cell formatting saved to the worksheet.

Meaning, the only way to know within VBA that the cell is orange is to duplicate your conditional formatting logic in VBA..

So when you say less than today, or you talking about a date? Or are you saying:

Day 1 Day 2 Day 3
5 4 9

Today
7



So Points 1 and 2 would be Orange since they are numerically less than todays result of 7?

If sould would need to know ranges: IE Chart data range is A1:G1, and Todays value is A4.

You will also need to define the color code you want the data points to be changed to. Since you cannot programmatically identify the color of a conditionally formated cell.

 

[tlindeman]

It doesn't have to be Conditional formating, it could if point 1's date is < today(), blue, else orange. the date is March 2011 for example. So the date is in cells B1:BU1. and the points are C1:CU1. So anywhere B1:BU1 is less than today, blue, else orange

THank You
Tony