Excel question

[Karoline]

Is there a way to do the following thing in excel. Say I have some data in the spreadsheet. Eg numbers . Some of them are highlighted due to certain reasons and such. These are put into a graph. Is there a way so that the graph has the same coloured bars than the highlighted cells?
So in other words the graph bars will be at least 2 different colours depending on if the data is highlighted or not?

[cassy34]

Hi Karoline, what version of Excel are you using?

Also, will the cells be coloured differently using conditional formatting or just changing the background of each cell as those 2 methods are accessed differently in VBA and if it's conditional formatting, it's a bastard to evaluate.

Oh, this will have to be done via a macro BTW.

[Karoline]

Hi, I think it is 2000 version. The cells are just highlighted, we don't use conditional formatting for it, we just change the background colour really.

[RUDE BOI]

I believe excel can only tell the colour of a cell, if it is excel 2007+.
So in your situation sadly I don't think it can be done.

You can have a drop down box next to each cell stating the colour, and the cell would change automatically (using conditioning formatting). Then this can be picked up by the graph (macro needed).

Edit: Having a quick google i realise it is possible, I must have been thinking about that fact your cant autofilter based on colour pre2007

[cassy34]

Have a go with the following code. Alternatively, if you post the spreadsheet I should be able to get it working. To get this working, press ALT+F11 to open a code window. Insert a new module (Insert > Module) from the top menu. The copy the following code in. To get it to run either press F5 or F8 to step through a line at a time.

Public Sub ColourGraph()

Dim c As Range
Dim x As Integer
x = 0
Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.ChartArea.Select

' Change the range for the one you're working with.
' This will change the colour of each bar to the background colour of the corresponding cell. You can play around with this by using an IF statement
For Each c In Worksheets("Sheet1").Range("B4:B17").Cells

x = x + 1
Worksheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1).Points(x).Select
With Selection.Interior
.ColorIndex = c.FormatConditions(1).Interior.ColorIndex
.Pattern = xlSolid
End With
Next


End Sub

[Karoline]

Thanks, i will try this, and let you know how i get on, thanks very much

[Karoline]

Hi, i tried this, but i am not sure if i am doing it correctly.
When i run it, the following error comes up ..

Run time error 1004 - Unable to get the ColorIndex property of the interior class...

[cassy34]

This may be a limitation of Excell 2000. I'll try and get hold of a copy and see if anything can be done.

[cassy34]

Hi Karoline.

I've installed Excel 2000 and tweaked the code a bit so hopefully this will do the trick. I'm assuming that this is a simple bar graph with only 1 range (series) defined. If not, i'll probably need to have a look at it to get a solution.

Public Sub ColourGraph()

Dim c As Range
Dim x As Integer
x = 0
Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.ChartArea.Select

' Change the range for the one you're working with.
' This will change the colour of each bar to the background colour of the corresponding cell. You can play around with this by using an IF statement

For Each c In Worksheets("Sheet1").Range("B4:B17").Cells

x = x + 1
ActiveChart.SeriesCollection(1).Points(x).Select
With Selection.Interior
.ColorIndex = c.Interior.ColorIndex
.Pattern = xlSolid
End With
Next


End Sub

[Karoline]

thanks very much again, will try this out tomorrow

[Karoline]

Wow, thanks very much. This worked great on 2000 Tested on 2003 also and i got it working, thanks very much.