How to keep specific bar color in chart when the bar change position in chart (conditional bar color)

Manojlo

New Member
Joined
Sep 4, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Hello

Is there any options to add specific color for single bar in chart, and bar to keep this color when change position in chart.
problem.jpg

For Example if we have two columns A i B, in A column (from A2:A5) we have words: Red, Blue, White, Yellow and Green, and in B column we have some values: 10, 20, 30, 40 and 50.
We make Clustered column chart in descending order, for example Green cell is on first place and have value 50, and I want to make this bar in green color even if I change values, for example 10 and it is not on first place anymore
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

can be done with code

VBA Code:
Sub COLOUR_GRAPH()
    ActiveSheet.ChartObjects("Chart 1").Activate
    For MY_ROWS = 2 To 6
        ActiveChart.SeriesCollection(1).Points(MY_ROWS - 1).Select
            With Selection.Interior
                Select Case Range("A" & MY_ROWS).Value
                    Case "Red"
                        MY_COLOUR = 3
                    Case "Yellow"
                        MY_COLOUR = 6
                    Case "White"
                        MY_COLOUR = 2
                    Case "Blue"
                        MY_COLOUR = 5
                    Case "Green"
                        MY_COLOUR = 10
                End Select
        .ColorIndex = MY_COLOUR
        .Pattern = xlSolid
            End With
    Next MY_ROWS
End Sub

could be added to a worksheet change event.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,222
Messages
5,623,472
Members
415,973
Latest member
johnemaile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top