+ Reply to Thread
Results 1 to 1 of 1

Multiple Series Conditional Formatting in a Line Graph

  1. #1
    Registered User
    Join Date
    05-15-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Multiple Series Conditional Formatting in a Line Graph

    Please HELP!!! I would really appreciate it if someone could have a look at the code and offer any suggestions. THANK YOU in advance.

    I'm new to VBA so I can use all the help I can get right now.

    I have 8 charts (maybe more will be added later) in the same worksheet ("Dashboard"). Each chart either has a minimum of 6 and a max of 7 series plus it may or may not have a "Target" line. Each series has 8 data points. Naming of series is consistant in all the charts (some charts might not have all the series or target line though)

    I need to colour code the series based on the name of the series (ie. "cat", "dog", "target", etc). I want to be able to write a macro so that it does this automatically for all the charts at once.

    Can someone help please?

    I have the following code that I've been working on, but it's not giving me what I want.
    ----
    Sub DoAll()

    Dim objCht As ChartObject

    For Each objCht In Sheets("Dashboard").ChartObjects
    Graphs objCht
    Next objCht

    End Sub

    Sub Graphs(Cht As ChartObject)
    '
    ' Graphs Macro

    With Cht.Chart
    With .ChartTitle
    .AutoScaleFont = True
    With .Font
    .Name = "Calibri"
    .FontStyle = "Bold"
    .Size = 9
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
    End With
    End With
    With .Axes(xlValue).TickLabels
    .AutoScaleFont = True
    With .Font
    .Name = "Calibri"
    .FontStyle = "Regular"
    .Size = 5
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
    End With
    End With
    With .Axes(xlCategory).TickLabels
    .AutoScaleFont = True
    With .Font
    .Name = "Calibri"
    .FontStyle = "Regular"
    .Size = 5
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
    End With
    End With
    With .Axes(xlValue).AxisTitle
    .AutoScaleFont = True
    With .Font
    .Name = "Calibri"
    .FontStyle = "Bold"
    .Size = 5
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
    End With
    End With
    With .SeriesCollection("All")
    .MarkerSize = 3
    .MarkerStyle = 8
    .MarkerBackgroundColor = RGB(55, 55, 150)
    .MarkerForegroundColor = RGB(55, 55, 150)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(55, 55, 150)
    End With

    With .SeriesCollection("Cat")
    .MarkerSize = 3
    .MarkerStyle = 2
    .MarkerBackgroundColor = RGB(56, 145, 167)
    .MarkerForegroundColor = RGB(56, 145, 167)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(56, 145, 167)
    End With

    With .SeriesCollection("Dog")
    .MarkerSize = 3
    .MarkerStyle = 1
    .MarkerBackgroundColor = RGB(195, 134, 13)
    .MarkerForegroundColor = RGB(195, 134, 13)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(195, 134, 13)
    End With

    With .SeriesCollection("Mouse")
    .MarkerSize = 3
    .MarkerStyle = 3
    .MarkerBackgroundColor = RGB(192, 0, 0)
    .MarkerForegroundColor = RGB(192, 0, 0)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(192, 0, 0)
    End With

    With .SeriesCollection("Fish")
    .MarkerSize = 3
    .MarkerStyle = 4
    .MarkerBackgroundColor = RGB(103, 166, 60)
    .MarkerForegroundColor = RGB(103, 166, 60)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(103, 166, 60)
    End With

    With .SeriesCollection("Turtle")
    .MarkerSize = 3
    .MarkerStyle = 5
    .MarkerBackgroundColor = RGB(145, 79, 171)
    .MarkerForegroundColor = RGB(145, 79, 171)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(145, 79, 171)
    End With

    With .SeriesCollection("Pig")
    .MarkerSize = 3
    .MarkerStyle = 6
    .MarkerBackgroundColor = RGB(119, 99, 53)
    .MarkerForegroundColor = RGB(119, 99, 53)
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(119, 99, 53)
    End With

    With .SeriesCollection("Target")
    .MarkerSize = 0
    .MarkerStyle = 0
    .Format.Line.Weight = 1.25
    .Border.Color = RGB(0, 0, 0)
    End With

    .Parent.RoundedCorners = True
    .Parent.Shadow = False

    End With

    End Sub
    Last edited by help_clueless!; 05-23-2012 at 12:40 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1