Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charts (Excel 2000)

    Hallo
    I want to know if there is a way to change the colour of bars depending on a selection for example Current or Future. I attach a sample for reference.
    Any help would be appreciated.

    Regards

    Kobus
    Regards
    Kobus

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts (Excel 2000)

    You could run this macro:

    Sub ColorChart()
    Dim sht As Worksheet
    Dim cht As Chart
    Dim ser As Series
    Dim i As Integer
    Dim n As Integer
    Set sht = ActiveWorkbook.Worksheets("Program")
    n = sht.Range("H65536").End(xlUp).Row
    Set cht = ActiveWorkbook.Charts("Chart1")
    Set ser = cht.SeriesCollection(2)
    For i = 1 To ser.Points.Count
    Select Case sht.Range("H" & (i + 1))
    Case "Current"
    ser.Points(i).Interior.Color = vbRed
    Case "Future"
    ser.Points(i).Interior.Color = vbGreen
    End Select
    Next i
    Set ser = Nothing
    Set cht = Nothing
    Set sht = Nothing
    End Sub

    It wil have to be run again if the data in column H change.

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charts (Excel 2000)

    Hans

    Thanks again to the genius.

    Regards

    Kobus
    Regards
    Kobus

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Charts (Excel 2000)

    Here is a non-macro method (needs 2 new data columns)

    Steve

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charts (Excel 2000)

    Steve

    Thank you also, it always amazes me how easy it seems when you guys give a solution
    Regards
    Kobus

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Charts (Excel 2000)

    The non-macro solution can be expanded to other "types" (just add more columns) and is also "live" and will change as the type changes, automatically.

    You can even add the legends for all the different types...

    Steve

Posting Permissions

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