Conditional Formatting Chart

[SSFG1992]

Dear All,

I would like to know how can I conditional format this chart:

Objective: "Change the color of graph to “Red” or “Green” depending on Achievement %age in cell “B40 to G40” in tab “PDV”. Currently, the graph colors are different (random colors).
Change the color of graph to “Red” or “Green” depending on Achievement %age in cell “B40 to G40” in tab “PDV”. "Currently, the graph colors are different (random colors).

So, basically I want the graph colours to change in either Red or Green depending on a percentage level.

Would be thankful for your help.

* I could not figure out how to upload attachment *. I can email the file if you share email address or please let me know how to upload the file"

Thanks.

 

[Tim_Excel_]

To apply conditional formatting with a formula:
Select B40:G40 > Tab Home > Conditional Formatting > New Rule > "Use a formula to determine whihc cells to format" > '=B40>0.5' (change the formula to your needs) > Format > Choose the colour > OK

If you want to have different colours, you need to set a new rule with another formula.

 

[SSFG1992]

Dear Tim,

I don't have to format the cell but the chart based on the value of cell.

 

[Tim_Excel_]

Excel does not support conditional formatting for charts. You could solve this issue by building a macro which would change the chart's colours based on a range's value. This would look like something along the lines of

IF 'condition' Then
With Chart1.FullseriesCollection(2).Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Transparency = 0
        .Solid
End With
End IF

Try recording a macro while editing the chart to your preferences, then edit the macro accordingly.

 

[Jon Peltier]

I have written a few tutorials about this:

Formula based
Conditional Formatting of Excel Charts

VBA based
VBA Conditional Formatting of Charts by Value
VBA Conditional Formatting of Charts by Series Name
VBA Conditional Formatting of Charts by Category Label