Close

Results 1 to 11 of 11

Thread: Excel question

  1. #1
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default Excel question

    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?

  2. #2
    DF Jedi cassy34's Avatar
    Join Date
    Nov 2004
    Location
    Lytham
    Posts
    1,453
    Thanks
    246
    Thanked:        181
    Karma Level
    341

    Default Re: Excel question

    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.
    Last edited by cassy34; 15th October 2010 at 03:42 PM.

  3. #3
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default

    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.

  4. #4
    DF PiMP RUDE BOI's Avatar
    Join Date
    Apr 2002
    Location
    E55EX BOY
    Posts
    264
    Thanks
    54
    Thanked:        16
    Karma Level
    252

    Default Re: Excel question

    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
    Last edited by RUDE BOI; 16th October 2010 at 06:01 PM.

  5. #5
    DF Jedi cassy34's Avatar
    Join Date
    Nov 2004
    Location
    Lytham
    Posts
    1,453
    Thanks
    246
    Thanked:        181
    Karma Level
    341

    Default Re: Excel question

    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

  6. #6
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default Re: Excel question

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

  7. #7
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default Re: Excel question

    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...

  8. #8
    DF Jedi cassy34's Avatar
    Join Date
    Nov 2004
    Location
    Lytham
    Posts
    1,453
    Thanks
    246
    Thanked:        181
    Karma Level
    341

    Default Re: Excel question

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

  9. #9
    DF Jedi cassy34's Avatar
    Join Date
    Nov 2004
    Location
    Lytham
    Posts
    1,453
    Thanks
    246
    Thanked:        181
    Karma Level
    341

    Default Re: Excel question

    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

  10. #10
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default

    thanks very much again, will try this out tomorrow

  11. #11
    DF Jedi Karoline's Avatar
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,556
    Thanks
    108
    Thanked:        60
    Karma Level
    438

    Default Re: Excel question

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

Similar Threads

  1. Excel question...
    By pete252 in forum PC Software
    Replies: 4
    Last Post: 3rd February 2010, 11:51 PM
  2. [HELP] Excel question
    By Karoline in forum PC Software
    Replies: 0
    Last Post: 3rd June 2009, 07:56 PM
  3. Excel question - formula
    By easy in forum The Dog and Duck
    Replies: 2
    Last Post: 22nd May 2007, 09:02 AM
  4. Excel formula question
    By Speedlock in forum Programming
    Replies: 7
    Last Post: 1st April 2007, 12:42 AM
  5. Excel Question
    By jackroo in forum PC Software
    Replies: 4
    Last Post: 13th September 2006, 11:48 AM

Social Networking Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •