lines/bar chart- on max valued bar, change color

I have the code below. Green bars and 80% yellow line and 90% red line. You
will also see I have the "max" value. I'd like that max column to be other
than the set green for all the bars. I set the colors near the end of the
program. I've looked at some of the examples at the support site, but I'm not
sure if any fits me. Thanks.
C O D E B E L O W...............
Sub Macro1()

'

' Macro1 Macro

' Macro recorded 3/12/2004 by bkondos

'



' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<

ChDir "H:\"

Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
StartRow:=1, _

DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _

:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _

Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _

TrailingMinusNumbers:=True

Columns("A:A").Select

Selection.NumberFormat = "m/d/yy h:mm;@"

Columns("B:B").Select

Selection.NumberFormat = "0.00"





Dim rng As Range

Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _

ActiveSheet.Cells(1, 1).End(xlDown))

rng.Offset(0, 3).Value = 80

rng.Offset(0, 4).Value = 90

Columns("D:D").Select

Selection.NumberFormat = "0.00"

Columns("E:E").Select

Selection.NumberFormat = "0.00"



' 91 and "f" is to thicken up the bar

rng.Offset(0, 5).Value = 91

Columns("F:F").Select

Selection.NumberFormat = "0.00"

' 2 lines below: move into cell K1, the last cell of a variable column
length A

Range("K1").Select

ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"

' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
length)

Range("K2").Select

ActiveCell.Formula = "=mid(K1, 1,10)"

' 5 lines copy cell k2 to k3, then drop /es

Range("K2").Select

Selection.Copy

Range("K3").Select

ActiveSheet.Paste

ActiveCell.Value = Replace(ActiveCell.Value, "/", "")



Range("G1").Select

ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"

Range("G2").Select

ActiveCell.FormulaR1C1 = "avg"



Range("H1").Select

ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"

Range("H2").Select

ActiveCell.FormulaR1C1 = "med"



Range("I1").Select

ActiveCell.FormulaR1C1 = "=max(C[-7])"

Range("I2").Select

ActiveCell.FormulaR1C1 = "max"



Range("I3").Select

ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"

Range("I4").Select

ActiveCell.FormulaR1C1 = "whenmax"



Range("H2,A:A,B:B,C:C,D:D,E:E").Select

Range("E1").Activate

Charts.Add

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column"

' 2 lines below changed into variable cells with code below these 2

' ActiveChart.SetSourceData
Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _

' :=xlColumns

Dim myrange As Range

Set myrange =
Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _

Sheets("sel60minsweek").Range("e1").End(xlDown))

ActiveChart.SetSourceData Source:=myrange, _

PlotBy:=xlColumns







ActiveChart.Location Where:=xlLocationAsNewSheet

With ActiveChart

.HasTitle = True

' was below with mm/dd/yy and got replcaed with k2 cell for date

' .ChartTitle.Characters.Text = _

' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
AVERAGE% WEEKLY MEDIAN% "





.ChartTitle.Characters.Text = _

"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
HIGHEST HOURLY CPU
ENDING " & Worksheets(1).Range("i3").Value & " " &
Worksheets(1).Range("i1") & " %"





.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _

"ENDING HOUR TIME"

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"

.Axes(xlCategory, xlSecondary).HasTitle = False

.Axes(xlValue, xlSecondary).HasTitle = False

End With

ActiveChart.Legend.Select

Selection.Delete

ActiveChart.SeriesCollection(1).Select

With Selection.Border

.Weight = xlThin

.LineStyle = xlAutomatic

End With

Selection.Shadow = False

Selection.InvertIfNegative = False

Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _

Variant:=2, _

Degree:=0.231372549019608

With Selection

.Fill.Visible = True

.Fill.ForeColor.SchemeColor = 50









' With Selection.Interior

' .ColorIndex = 43

' .Pattern = xlSolid

End With

ActiveChart.SeriesCollection(3).Select

With Selection.Border

.ColorIndex = 57

.Weight = xlThick

.LineStyle = xlContinuous

End With

With Selection

.MarkerBackgroundColorIndex = xlAutomatic

.MarkerForegroundColorIndex = xlAutomatic

.MarkerStyle = xlNone

.Smooth = False

.MarkerSize = 9

.Shadow = False

End With

ActiveChart.SeriesCollection(4).Select

With Selection.Border

.ColorIndex = 3

.Weight = xlThick

.LineStyle = xlContinuous

End With

With Selection

.MarkerBackgroundColorIndex = xlNone

.MarkerForegroundColorIndex = xlAutomatic

.MarkerStyle = xlNone

.Smooth = False

.MarkerSize = 5

.Shadow = False

End With

ActiveChart.PlotArea.Select

With ActiveChart.TextBoxes.Add(337, 230, 48, 18)

.Select

.AutoSize = True

.Formula = "=sel60minsweek!$G$1"

End With

Selection.ShapeRange.IncrementLeft -11.44

Selection.ShapeRange.IncrementTop -203.49

ActiveChart.PlotArea.Select

With ActiveChart.TextBoxes.Add(387, 230, 48, 18)

.Select

.AutoSize = True

.Formula = "=sel60minsweek!$H$1"

End With

Selection.ShapeRange.IncrementLeft 104.2

Selection.ShapeRange.IncrementTop -203.49





With ActiveChart.PageSetup

.LeftHeader = ""

.CenterHeader = ""

.RightHeader = ""

.LeftFooter = ""

.CenterFooter = ""

.RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.75)

.RightMargin = Application.InchesToPoints(0.75)

.TopMargin = Application.InchesToPoints(1)

.BottomMargin = Application.InchesToPoints(1)

.HeaderMargin = Application.InchesToPoints(0.5)

.FooterMargin = Application.InchesToPoints(0.5)

.ChartSize = xlFullPage

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlLandscape

.Draft = False

.PaperSize = xlPaperLetter

.FirstPageNumber = xlAutomatic

.BlackAndWhite = False

.Zoom = 100

End With

ActiveChart.Deselect



' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _

' " & Worksheets(1).Range("k2").Value & "











ChDir "H:\MY DOCUMENTS ON H DRIVE"

ActiveWorkbook.SaveAs Filename:= _

"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
& ".xls", FileFormat:=xlNormal, _

Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

CreateBackup:=False

End Sub



--
BOB-THE-K

Bob -

Maybe you could adapt the following conditional charting technique, by
moving the maximum value to its own series which you've formatted with
the other color:

http://peltiertech.com/Excel/Charts/...nalChart1.html

This page may also give you an idea:

http://peltiertech.com/Excel/Charts/FormatMinMax.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

BOB-THE-K wrote:

> I have the code below. Green bars and 80% yellow line and 90% red line. You
> will also see I have the "max" value. I'd like that max column to be other
> than the set green for all the bars. I set the colors near the end of the
> program. I've looked at some of the examples at the support site, but I'm not
> sure if any fits me. Thanks.
> C O D E B E L O W...............
> Sub Macro1()
>
> '
>
> ' Macro1 Macro
>
> ' Macro recorded 3/12/2004 by bkondos
>
> '
>
>
>
> ' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
>
> ChDir "H:\"
>
> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
> StartRow:=1, _
>
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
> ConsecutiveDelimiter _
>
> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
>
> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
>
> TrailingMinusNumbers:=True
>
> Columns("A:A").Select
>
> Selection.NumberFormat = "m/d/yy h:mm;@"
>
> Columns("B:B").Select
>
> Selection.NumberFormat = "0.00"
>
>
>
>
>
> Dim rng As Range
>
> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
>
> ActiveSheet.Cells(1, 1).End(xlDown))
>
> rng.Offset(0, 3).Value = 80
>
> rng.Offset(0, 4).Value = 90
>
> Columns("D:D").Select
>
> Selection.NumberFormat = "0.00"
>
> Columns("E:E").Select
>
> Selection.NumberFormat = "0.00"
>
>
>
> ' 91 and "f" is to thicken up the bar
>
> rng.Offset(0, 5).Value = 91
>
> Columns("F:F").Select
>
> Selection.NumberFormat = "0.00"
>
> ' 2 lines below: move into cell K1, the last cell of a variable column
> length A
>
> Range("K1").Select
>
> ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
>
> ' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
> length)
>
> Range("K2").Select
>
> ActiveCell.Formula = "=mid(K1, 1,10)"
>
> ' 5 lines copy cell k2 to k3, then drop /es
>
> Range("K2").Select
>
> Selection.Copy
>
> Range("K3").Select
>
> ActiveSheet.Paste
>
> ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
>
>
>
> Range("G1").Select
>
> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
>
> Range("G2").Select
>
> ActiveCell.FormulaR1C1 = "avg"
>
>
>
> Range("H1").Select
>
> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
>
> Range("H2").Select
>
> ActiveCell.FormulaR1C1 = "med"
>
>
>
> Range("I1").Select
>
> ActiveCell.FormulaR1C1 = "=max(C[-7])"
>
> Range("I2").Select
>
> ActiveCell.FormulaR1C1 = "max"
>
>
>
> Range("I3").Select
>
> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
>
> Range("I4").Select
>
> ActiveCell.FormulaR1C1 = "whenmax"
>
>
>
> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
>
> Range("E1").Activate
>
> Charts.Add
>
> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
> Column"
>
> ' 2 lines below changed into variable cells with code below these 2
>
> ' ActiveChart.SetSourceData
> Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
>
> ' :=xlColumns
>
> Dim myrange As Range
>
> Set myrange =
> Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
>
> Sheets("sel60minsweek").Range("e1").End(xlDown))
>
> ActiveChart.SetSourceData Source:=myrange, _
>
> PlotBy:=xlColumns
>
>
>
>
>
>
>
> ActiveChart.Location Where:=xlLocationAsNewSheet
>
> With ActiveChart
>
> .HasTitle = True
>
> ' was below with mm/dd/yy and got replcaed with k2 cell for date
>
> ' .ChartTitle.Characters.Text = _
>
> ' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
> AVERAGE% WEEKLY MEDIAN% "
>
>
>
>
>
> .ChartTitle.Characters.Text = _
>
> "W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
> TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
> HIGHEST HOURLY CPU
> ENDING " & Worksheets(1).Range("i3").Value & " " &
> Worksheets(1).Range("i1") & " %"
>
>
>
>
>
> .Axes(xlCategory, xlPrimary).HasTitle = True
>
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
>
> "ENDING HOUR TIME"
>
> .Axes(xlValue, xlPrimary).HasTitle = True
>
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
>
> .Axes(xlCategory, xlSecondary).HasTitle = False
>
> .Axes(xlValue, xlSecondary).HasTitle = False
>
> End With
>
> ActiveChart.Legend.Select
>
> Selection.Delete
>
> ActiveChart.SeriesCollection(1).Select
>
> With Selection.Border
>
> .Weight = xlThin
>
> .LineStyle = xlAutomatic
>
> End With
>
> Selection.Shadow = False
>
> Selection.InvertIfNegative = False
>
> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
>
> Variant:=2, _
>
> Degree:=0.231372549019608
>
> With Selection
>
> .Fill.Visible = True
>
> .Fill.ForeColor.SchemeColor = 50
>
>
>
>
>
>
>
>
>
> ' With Selection.Interior
>
> ' .ColorIndex = 43
>
> ' .Pattern = xlSolid
>
> End With
>
> ActiveChart.SeriesCollection(3).Select
>
> With Selection.Border
>
> .ColorIndex = 57
>
> .Weight = xlThick
>
> .LineStyle = xlContinuous
>
> End With
>
> With Selection
>
> .MarkerBackgroundColorIndex = xlAutomatic
>
> .MarkerForegroundColorIndex = xlAutomatic
>
> .MarkerStyle = xlNone
>
> .Smooth = False
>
> .MarkerSize = 9
>
> .Shadow = False
>
> End With
>
> ActiveChart.SeriesCollection(4).Select
>
> With Selection.Border
>
> .ColorIndex = 3
>
> .Weight = xlThick
>
> .LineStyle = xlContinuous
>
> End With
>
> With Selection
>
> .MarkerBackgroundColorIndex = xlNone
>
> .MarkerForegroundColorIndex = xlAutomatic
>
> .MarkerStyle = xlNone
>
> .Smooth = False
>
> .MarkerSize = 5
>
> .Shadow = False
>
> End With
>
> ActiveChart.PlotArea.Select
>
> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
>
> .Select
>
> .AutoSize = True
>
> .Formula = "=sel60minsweek!$G$1"
>
> End With
>
> Selection.ShapeRange.IncrementLeft -11.44
>
> Selection.ShapeRange.IncrementTop -203.49
>
> ActiveChart.PlotArea.Select
>
> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
>
> .Select
>
> .AutoSize = True
>
> .Formula = "=sel60minsweek!$H$1"
>
> End With
>
> Selection.ShapeRange.IncrementLeft 104.2
>
> Selection.ShapeRange.IncrementTop -203.49
>
>
>
>
>
> With ActiveChart.PageSetup
>
> .LeftHeader = ""
>
> .CenterHeader = ""
>
> .RightHeader = ""
>
> .LeftFooter = ""
>
> .CenterFooter = ""
>
> .RightFooter = ""
>
> .LeftMargin = Application.InchesToPoints(0.75)
>
> .RightMargin = Application.InchesToPoints(0.75)
>
> .TopMargin = Application.InchesToPoints(1)
>
> .BottomMargin = Application.InchesToPoints(1)
>
> .HeaderMargin = Application.InchesToPoints(0.5)
>
> .FooterMargin = Application.InchesToPoints(0.5)
>
> .ChartSize = xlFullPage
>
> .PrintQuality = 600
>
> .CenterHorizontally = False
>
> .CenterVertically = False
>
> .Orientation = xlLandscape
>
> .Draft = False
>
> .PaperSize = xlPaperLetter
>
> .FirstPageNumber = xlAutomatic
>
> .BlackAndWhite = False
>
> .Zoom = 100
>
> End With
>
> ActiveChart.Deselect
>
>
>
> ' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
>
> ' " & Worksheets(1).Range("k2").Value & "
>
>
>
>
>
>
>
>
>
>
>
> ChDir "H:\MY DOCUMENTS ON H DRIVE"
>
> ActiveWorkbook.SaveAs Filename:= _
>
> "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
> & ".xls", FileFormat:=xlNormal, _
>
> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
>
> CreateBackup:=False
>
> End Sub
>
>
>

The conditional sure looks like where I'd like to be, but I can't quite grasp
how to set for the 1 highest entry in COL B", and not ranges as are shown in
the example..



HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
column I'd like to light up. Any more clues are appreciated.
A B c D E
02/21/200509.59.00 10.27 80 90

I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
So wherever 65 shows up in the chart, that's what I'd like different
colored. 65 by itself is not a range, I can't see how to adapt to your
example. The 80 and 90 are percentage max lines left to right, while col b
gets turned to up/down bars on the graph. Thanks again for whatever you
give.


"Jon Peltier" wrote:

> Bob -
>
> Maybe you could adapt the following conditional charting technique, by
> moving the maximum value to its own series which you've formatted with
> the other color:
>
> http://peltiertech.com/Excel/Charts/...nalChart1.html
>
> This page may also give you an idea:
>
> http://peltiertech.com/Excel/Charts/FormatMinMax.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> BOB-THE-K wrote:
>
> > I have the code below. Green bars and 80% yellow line and 90% red line. You
> > will also see I have the "max" value. I'd like that max column to be other
> > than the set green for all the bars. I set the colors near the end of the
> > program. I've looked at some of the examples at the support site, but I'm not
> > sure if any fits me. Thanks.
> > C O D E B E L O W...............
> > Sub Macro1()
> >
> > '
> >
> > ' Macro1 Macro
> >
> > ' Macro recorded 3/12/2004 by bkondos
> >
> > '
> >
> >
> >
> > ' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
> >
> > ChDir "H:\"
> >
> > Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
> > StartRow:=1, _
> >
> > DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
> > ConsecutiveDelimiter _
> >
> > :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
> >
> > Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
> >
> > TrailingMinusNumbers:=True
> >
> > Columns("A:A").Select
> >
> > Selection.NumberFormat = "m/d/yy h:mm;@"
> >
> > Columns("B:B").Select
> >
> > Selection.NumberFormat = "0.00"
> >
> >
> >
> >
> >
> > Dim rng As Range
> >
> > Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
> >
> > ActiveSheet.Cells(1, 1).End(xlDown))
> >
> > rng.Offset(0, 3).Value = 80
> >
> > rng.Offset(0, 4).Value = 90
> >
> > Columns("D:D").Select
> >
> > Selection.NumberFormat = "0.00"
> >
> > Columns("E:E").Select
> >
> > Selection.NumberFormat = "0.00"
> >
> >
> >
> > ' 91 and "f" is to thicken up the bar
> >
> > rng.Offset(0, 5).Value = 91
> >
> > Columns("F:F").Select
> >
> > Selection.NumberFormat = "0.00"
> >
> > ' 2 lines below: move into cell K1, the last cell of a variable column
> > length A
> >
> > Range("K1").Select
> >
> > ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
> >
> > ' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
> > length)
> >
> > Range("K2").Select
> >
> > ActiveCell.Formula = "=mid(K1, 1,10)"
> >
> > ' 5 lines copy cell k2 to k3, then drop /es
> >
> > Range("K2").Select
> >
> > Selection.Copy
> >
> > Range("K3").Select
> >
> > ActiveSheet.Paste
> >
> > ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
> >
> >
> >
> > Range("G1").Select
> >
> > ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
> >
> > Range("G2").Select
> >
> > ActiveCell.FormulaR1C1 = "avg"
> >
> >
> >
> > Range("H1").Select
> >
> > ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
> >
> > Range("H2").Select
> >
> > ActiveCell.FormulaR1C1 = "med"
> >
> >
> >
> > Range("I1").Select
> >
> > ActiveCell.FormulaR1C1 = "=max(C[-7])"
> >
> > Range("I2").Select
> >
> > ActiveCell.FormulaR1C1 = "max"
> >
> >
> >
> > Range("I3").Select
> >
> > ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
> >
> > Range("I4").Select
> >
> > ActiveCell.FormulaR1C1 = "whenmax"
> >
> >
> >
> > Range("H2,A:A,B:B,C:C,D:D,E:E").Select
> >
> > Range("E1").Activate
> >
> > Charts.Add
> >
> > ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
> > Column"
> >
> > ' 2 lines below changed into variable cells with code below these 2
> >
> > ' ActiveChart.SetSourceData
> > Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
> >
> > ' :=xlColumns
> >
> > Dim myrange As Range
> >
> > Set myrange =
> > Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
> >
> > Sheets("sel60minsweek").Range("e1").End(xlDown))
> >
> > ActiveChart.SetSourceData Source:=myrange, _
> >
> > PlotBy:=xlColumns
> >
> >
> >
> >
> >
> >
> >
> > ActiveChart.Location Where:=xlLocationAsNewSheet
> >
> > With ActiveChart
> >
> > .HasTitle = True
> >
> > ' was below with mm/dd/yy and got replcaed with k2 cell for date
> >
> > ' .ChartTitle.Characters.Text = _
> >
> > ' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
> > AVERAGE% WEEKLY MEDIAN% "
> >
> >
> >
> >
> >
> > .ChartTitle.Characters.Text = _
> >
> > "W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
> > TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
> > HIGHEST HOURLY CPU
> > ENDING " & Worksheets(1).Range("i3").Value & " " &
> > Worksheets(1).Range("i1") & " %"
> >
> >
> >
> >
> >
> > .Axes(xlCategory, xlPrimary).HasTitle = True
> >
> > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
> >
> > "ENDING HOUR TIME"
> >
> > .Axes(xlValue, xlPrimary).HasTitle = True
> >
> > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
> >
> > .Axes(xlCategory, xlSecondary).HasTitle = False
> >
> > .Axes(xlValue, xlSecondary).HasTitle = False
> >
> > End With
> >
> > ActiveChart.Legend.Select
> >
> > Selection.Delete
> >
> > ActiveChart.SeriesCollection(1).Select
> >
> > With Selection.Border
> >
> > .Weight = xlThin
> >
> > .LineStyle = xlAutomatic
> >
> > End With
> >
> > Selection.Shadow = False
> >
> > Selection.InvertIfNegative = False
> >
> > Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
> >
> > Variant:=2, _
> >
> > Degree:=0.231372549019608
> >
> > With Selection
> >
> > .Fill.Visible = True
> >
> > .Fill.ForeColor.SchemeColor = 50
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ' With Selection.Interior
> >
> > ' .ColorIndex = 43
> >
> > ' .Pattern = xlSolid
> >
> > End With
> >
> > ActiveChart.SeriesCollection(3).Select
> >
> > With Selection.Border
> >
> > .ColorIndex = 57
> >
> > .Weight = xlThick
> >
> > .LineStyle = xlContinuous
> >
> > End With
> >
> > With Selection
> >
> > .MarkerBackgroundColorIndex = xlAutomatic
> >
> > .MarkerForegroundColorIndex = xlAutomatic
> >
> > .MarkerStyle = xlNone
> >
> > .Smooth = False
> >
> > .MarkerSize = 9
> >
> > .Shadow = False
> >
> > End With
> >
> > ActiveChart.SeriesCollection(4).Select
> >
> > With Selection.Border
> >
> > .ColorIndex = 3
> >
> > .Weight = xlThick
> >
> > .LineStyle = xlContinuous
> >
> > End With
> >
> > With Selection
> >
> > .MarkerBackgroundColorIndex = xlNone
> >
> > .MarkerForegroundColorIndex = xlAutomatic
> >
> > .MarkerStyle = xlNone
> >
> > .Smooth = False
> >
> > .MarkerSize = 5
> >
> > .Shadow = False
> >
> > End With
> >
> > ActiveChart.PlotArea.Select
> >
> > With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
> >
> > .Select
> >
> > .AutoSize = True
> >
> > .Formula = "=sel60minsweek!$G$1"
> >
> > End With
> >
> > Selection.ShapeRange.IncrementLeft -11.44
> >
> > Selection.ShapeRange.IncrementTop -203.49
> >
> > ActiveChart.PlotArea.Select
> >
> > With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
> >
> > .Select
> >
> > .AutoSize = True
> >
> > .Formula = "=sel60minsweek!$H$1"
> >
> > End With
> >
> > Selection.ShapeRange.IncrementLeft 104.2
> >
> > Selection.ShapeRange.IncrementTop -203.49
> >
> >
> >
> >
> >
> > With ActiveChart.PageSetup
> >
> > .LeftHeader = ""
> >
> > .CenterHeader = ""
> >
> > .RightHeader = ""
> >
> > .LeftFooter = ""
> >
> > .CenterFooter = ""
> >
> > .RightFooter = ""
> >
> > .LeftMargin = Application.InchesToPoints(0.75)
> >
> > .RightMargin = Application.InchesToPoints(0.75)
> >
> > .TopMargin = Application.InchesToPoints(1)
> >
> > .BottomMargin = Application.InchesToPoints(1)
> >
> > .HeaderMargin = Application.InchesToPoints(0.5)
> >
> > .FooterMargin = Application.InchesToPoints(0.5)
> >
> > .ChartSize = xlFullPage
> >
> > .PrintQuality = 600
> >
> > .CenterHorizontally = False
> >
> > .CenterVertically = False
> >
> > .Orientation = xlLandscape
> >
> > .Draft = False
> >
> > .PaperSize = xlPaperLetter
> >
> > .FirstPageNumber = xlAutomatic
> >
> > .BlackAndWhite = False
> >
> > .Zoom = 100
> >
> > End With
> >
> > ActiveChart.Deselect
> >
> >
> >
> > ' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
> >
> > ' " & Worksheets(1).Range("k2").Value & "
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ChDir "H:\MY DOCUMENTS ON H DRIVE"
> >
> > ActiveWorkbook.SaveAs Filename:= _
> >
> > "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
> > & ".xls", FileFormat:=xlNormal, _
> >
> > Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> >
> > CreateBackup:=False
> >
> > End Sub
> >
> >
> >
>

Bob -

Your data looks a little funny, but here goes.

Supposing A2:A20 has your X categories, and B2:B20 has your values for the column
series. Keep A1 blank, put a label in B1. Insert two columns after B, and put the
labels "Green" and "Max" in the new C1 and D1. In C2 enter this formula:

=IF(B2<MAX(B$2:B$20),B2,0)

and fill down to C20 with this. In D2 enter this formula:

=IF(B2=MAX(B$2:B$20),B2,0)

and fill this down to D20. Your maximum will show up in column D, and all others
will be in C.

Instead of plotting column B, plot both C and D, as either stacked columns, or as
clustered columns with an overlap of 100% (Overlap is on the Format Series dialog,
Options tab). Color the two series differently, then embellish with all the rest of
your lines.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

> The conditional sure looks like where I'd like to be, but I can't quite grasp
> how to set for the 1 highest entry in COL B", and not ranges as are shown in
> the example..
>
>
>
> HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
> column I'd like to light up. Any more clues are appreciated.
> A B c D E
> 02/21/200509.59.00 10.27 80 90
>
> I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
> So wherever 65 shows up in the chart, that's what I'd like different
> colored. 65 by itself is not a range, I can't see how to adapt to your
> example. The 80 and 90 are percentage max lines left to right, while col b
> gets turned to up/down bars on the graph. Thanks again for whatever you
> give.
>
>
> "Jon Peltier" wrote:
>
>
>>Bob -
>>
>>Maybe you could adapt the following conditional charting technique, by
>>moving the maximum value to its own series which you've formatted with
>>the other color:
>>
>> http://peltiertech.com/Excel/Charts/...nalChart1.html
>>
>>This page may also give you an idea:
>>
>> http://peltiertech.com/Excel/Charts/FormatMinMax.html
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>BOB-THE-K wrote:
>>
>>
>>>I have the code below. Green bars and 80% yellow line and 90% red line. You
>>>will also see I have the "max" value. I'd like that max column to be other
>>>than the set green for all the bars. I set the colors near the end of the
>>>program. I've looked at some of the examples at the support site, but I'm not
>>>sure if any fits me. Thanks.
>>>C O D E B E L O W...............
>>>Sub Macro1()
>>>
>>>'
>>>
>>>' Macro1 Macro
>>>
>>>' Macro recorded 3/12/2004 by bkondos
>>>
>>>'
>>>
>>>
>>>
>>>' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
>>>
>>> ChDir "H:\"
>>>
>>> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
>>>StartRow:=1, _
>>>
>>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
>>>ConsecutiveDelimiter _
>>>
>>> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
>>>
>>> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
>>>
>>> TrailingMinusNumbers:=True
>>>
>>> Columns("A:A").Select
>>>
>>> Selection.NumberFormat = "m/d/yy h:mm;@"
>>>
>>> Columns("B:B").Select
>>>
>>> Selection.NumberFormat = "0.00"
>>>
>>>
>>>
>>>
>>>
>>> Dim rng As Range
>>>
>>> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
>>>
>>> ActiveSheet.Cells(1, 1).End(xlDown))
>>>
>>> rng.Offset(0, 3).Value = 80
>>>
>>> rng.Offset(0, 4).Value = 90
>>>
>>> Columns("D:D").Select
>>>
>>> Selection.NumberFormat = "0.00"
>>>
>>> Columns("E:E").Select
>>>
>>> Selection.NumberFormat = "0.00"
>>>
>>>
>>>
>>>' 91 and "f" is to thicken up the bar
>>>
>>> rng.Offset(0, 5).Value = 91
>>>
>>> Columns("F:F").Select
>>>
>>> Selection.NumberFormat = "0.00"
>>>
>>>' 2 lines below: move into cell K1, the last cell of a variable column
>>>length A
>>>
>>> Range("K1").Select
>>>
>>> ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
>>>
>>>' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
>>>length)
>>>
>>> Range("K2").Select
>>>
>>> ActiveCell.Formula = "=mid(K1, 1,10)"
>>>
>>>' 5 lines copy cell k2 to k3, then drop /es
>>>
>>> Range("K2").Select
>>>
>>> Selection.Copy
>>>
>>> Range("K3").Select
>>>
>>> ActiveSheet.Paste
>>>
>>> ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
>>>
>>>
>>>
>>> Range("G1").Select
>>>
>>> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
>>>
>>> Range("G2").Select
>>>
>>> ActiveCell.FormulaR1C1 = "avg"
>>>
>>>
>>>
>>> Range("H1").Select
>>>
>>> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
>>>
>>> Range("H2").Select
>>>
>>> ActiveCell.FormulaR1C1 = "med"
>>>
>>>
>>>
>>> Range("I1").Select
>>>
>>> ActiveCell.FormulaR1C1 = "=max(C[-7])"
>>>
>>> Range("I2").Select
>>>
>>> ActiveCell.FormulaR1C1 = "max"
>>>
>>>
>>>
>>> Range("I3").Select
>>>
>>> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
>>>
>>> Range("I4").Select
>>>
>>> ActiveCell.FormulaR1C1 = "whenmax"
>>>
>>>
>>>
>>> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
>>>
>>> Range("E1").Activate
>>>
>>> Charts.Add
>>>
>>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
>>>Column"
>>>
>>>' 2 lines below changed into variable cells with code below these 2
>>>
>>>' ActiveChart.SetSourceData
>>>Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
>>>
>>>' :=xlColumns
>>>
>>>Dim myrange As Range
>>>
>>>Set myrange =
>>>Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
>>>
>>> Sheets("sel60minsweek").Range("e1").End(xlDown))
>>>
>>>ActiveChart.SetSourceData Source:=myrange, _
>>>
>>> PlotBy:=xlColumns
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ActiveChart.Location Where:=xlLocationAsNewSheet
>>>
>>> With ActiveChart
>>>
>>> .HasTitle = True
>>>
>>>' was below with mm/dd/yy and got replcaed with k2 cell for date
>>>
>>>' .ChartTitle.Characters.Text = _
>>>
>>>' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
>>>AVERAGE% WEEKLY MEDIAN% "
>>>
>>>
>>>
>>>
>>>
>>> .ChartTitle.Characters.Text = _
>>>
>>>"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
>>>TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
>>> HIGHEST HOURLY CPU
>>>ENDING " & Worksheets(1).Range("i3").Value & " " &
>>>Worksheets(1).Range("i1") & " %"
>>>
>>>
>>>
>>>
>>>
>>> .Axes(xlCategory, xlPrimary).HasTitle = True
>>>
>>> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
>>>
>>> "ENDING HOUR TIME"
>>>
>>> .Axes(xlValue, xlPrimary).HasTitle = True
>>>
>>> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
>>>
>>> .Axes(xlCategory, xlSecondary).HasTitle = False
>>>
>>> .Axes(xlValue, xlSecondary).HasTitle = False
>>>
>>> End With
>>>
>>> ActiveChart.Legend.Select
>>>
>>> Selection.Delete
>>>
>>> ActiveChart.SeriesCollection(1).Select
>>>
>>> With Selection.Border
>>>
>>> .Weight = xlThin
>>>
>>> .LineStyle = xlAutomatic
>>>
>>> End With
>>>
>>> Selection.Shadow = False
>>>
>>> Selection.InvertIfNegative = False
>>>
>>> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
>>>
>>> Variant:=2, _
>>>
>>> Degree:=0.231372549019608
>>>
>>> With Selection
>>>
>>> .Fill.Visible = True
>>>
>>> .Fill.ForeColor.SchemeColor = 50
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>' With Selection.Interior
>>>
>>>' .ColorIndex = 43
>>>
>>>' .Pattern = xlSolid
>>>
>>> End With
>>>
>>> ActiveChart.SeriesCollection(3).Select
>>>
>>> With Selection.Border
>>>
>>> .ColorIndex = 57
>>>
>>> .Weight = xlThick
>>>
>>> .LineStyle = xlContinuous
>>>
>>> End With
>>>
>>> With Selection
>>>
>>> .MarkerBackgroundColorIndex = xlAutomatic
>>>
>>> .MarkerForegroundColorIndex = xlAutomatic
>>>
>>> .MarkerStyle = xlNone
>>>
>>> .Smooth = False
>>>
>>> .MarkerSize = 9
>>>
>>> .Shadow = False
>>>
>>> End With
>>>
>>> ActiveChart.SeriesCollection(4).Select
>>>
>>> With Selection.Border
>>>
>>> .ColorIndex = 3
>>>
>>> .Weight = xlThick
>>>
>>> .LineStyle = xlContinuous
>>>
>>> End With
>>>
>>> With Selection
>>>
>>> .MarkerBackgroundColorIndex = xlNone
>>>
>>> .MarkerForegroundColorIndex = xlAutomatic
>>>
>>> .MarkerStyle = xlNone
>>>
>>> .Smooth = False
>>>
>>> .MarkerSize = 5
>>>
>>> .Shadow = False
>>>
>>> End With
>>>
>>> ActiveChart.PlotArea.Select
>>>
>>> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
>>>
>>> .Select
>>>
>>> .AutoSize = True
>>>
>>> .Formula = "=sel60minsweek!$G$1"
>>>
>>> End With
>>>
>>> Selection.ShapeRange.IncrementLeft -11.44
>>>
>>> Selection.ShapeRange.IncrementTop -203.49
>>>
>>> ActiveChart.PlotArea.Select
>>>
>>> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
>>>
>>> .Select
>>>
>>> .AutoSize = True
>>>
>>> .Formula = "=sel60minsweek!$H$1"
>>>
>>> End With
>>>
>>> Selection.ShapeRange.IncrementLeft 104.2
>>>
>>> Selection.ShapeRange.IncrementTop -203.49
>>>
>>>
>>>
>>>
>>>
>>> With ActiveChart.PageSetup
>>>
>>> .LeftHeader = ""
>>>
>>> .CenterHeader = ""
>>>
>>> .RightHeader = ""
>>>
>>> .LeftFooter = ""
>>>
>>> .CenterFooter = ""
>>>
>>> .RightFooter = ""
>>>
>>> .LeftMargin = Application.InchesToPoints(0.75)
>>>
>>> .RightMargin = Application.InchesToPoints(0.75)
>>>
>>> .TopMargin = Application.InchesToPoints(1)
>>>
>>> .BottomMargin = Application.InchesToPoints(1)
>>>
>>> .HeaderMargin = Application.InchesToPoints(0.5)
>>>
>>> .FooterMargin = Application.InchesToPoints(0.5)
>>>
>>> .ChartSize = xlFullPage
>>>
>>> .PrintQuality = 600
>>>
>>> .CenterHorizontally = False
>>>
>>> .CenterVertically = False
>>>
>>> .Orientation = xlLandscape
>>>
>>> .Draft = False
>>>
>>> .PaperSize = xlPaperLetter
>>>
>>> .FirstPageNumber = xlAutomatic
>>>
>>> .BlackAndWhite = False
>>>
>>> .Zoom = 100
>>>
>>> End With
>>>
>>> ActiveChart.Deselect
>>>
>>>
>>>
>>>' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
>>>
>>>' " & Worksheets(1).Range("k2").Value & "
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ChDir "H:\MY DOCUMENTS ON H DRIVE"
>>>
>>> ActiveWorkbook.SaveAs Filename:= _
>>>
>>>"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
>>>& ".xls", FileFormat:=xlNormal, _
>>>
>>> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
>>>
>>> CreateBackup:=False
>>>
>>>End Sub
>>>
>>>
>>>
>>

Followed your code and I got the columns with MAX as a different color.
That's great. One more go at this and I think I'll have it. Remember, I
originally showed that this was a "LINES AND COLUMNS" chart. COL A is
date/timeinfo COL B is cpu% used COL C is blank COL D is filled with all 80s
(to make a 80% threshhold line, COL E all 90 for 90%).


With the chart you had me build, how do I get those lines (80 90) back?
2nd and final... my current charting is done in a macro already, with COL A
and COL B having variable no. of cells. How do I tweek those "MAX" formulas
for variable. Once again, thank you for your time. I probably will be able to
complete my task after the above 2 questions have been answered.

"Jon Peltier" wrote:

> Bob -
>
> Your data looks a little funny, but here goes.
>
> Supposing A2:A20 has your X categories, and B2:B20 has your values for the column
> series. Keep A1 blank, put a label in B1. Insert two columns after B, and put the
> labels "Green" and "Max" in the new C1 and D1. In C2 enter this formula:
>
> =IF(B2<MAX(B$2:B$20),B2,0)
>
> and fill down to C20 with this. In D2 enter this formula:
>
> =IF(B2=MAX(B$2:B$20),B2,0)
>
> and fill this down to D20. Your maximum will show up in column D, and all others
> will be in C.
>
> Instead of plotting column B, plot both C and D, as either stacked columns, or as
> clustered columns with an overlap of 100% (Overlap is on the Format Series dialog,
> Options tab). Color the two series differently, then embellish with all the rest of
> your lines.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> > The conditional sure looks like where I'd like to be, but I can't quite grasp
> > how to set for the 1 highest entry in COL B", and not ranges as are shown in
> > the example..
> >
> >
> >
> > HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
> > column I'd like to light up. Any more clues are appreciated.
> > A B c D E
> > 02/21/200509.59.00 10.27 80 90
> >
> > I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
> > So wherever 65 shows up in the chart, that's what I'd like different
> > colored. 65 by itself is not a range, I can't see how to adapt to your
> > example. The 80 and 90 are percentage max lines left to right, while col b
> > gets turned to up/down bars on the graph. Thanks again for whatever you
> > give.
> >
> >
> > "Jon Peltier" wrote:
> >
> >
> >>Bob -
> >>
> >>Maybe you could adapt the following conditional charting technique, by
> >>moving the maximum value to its own series which you've formatted with
> >>the other color:
> >>
> >> http://peltiertech.com/Excel/Charts/...nalChart1.html
> >>
> >>This page may also give you an idea:
> >>
> >> http://peltiertech.com/Excel/Charts/FormatMinMax.html
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>BOB-THE-K wrote:
> >>
> >>
> >>>I have the code below. Green bars and 80% yellow line and 90% red line. You
> >>>will also see I have the "max" value. I'd like that max column to be other
> >>>than the set green for all the bars. I set the colors near the end of the
> >>>program. I've looked at some of the examples at the support site, but I'm not
> >>>sure if any fits me. Thanks.
> >>>C O D E B E L O W...............
> >>>Sub Macro1()
> >>>
> >>>'
> >>>
> >>>' Macro1 Macro
> >>>
> >>>' Macro recorded 3/12/2004 by bkondos
> >>>
> >>>'
> >>>
> >>>
> >>>
> >>>' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
> >>>
> >>> ChDir "H:\"
> >>>
> >>> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
> >>>StartRow:=1, _
> >>>
> >>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
> >>>ConsecutiveDelimiter _
> >>>
> >>> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
> >>>
> >>> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
> >>>
> >>> TrailingMinusNumbers:=True
> >>>
> >>> Columns("A:A").Select
> >>>
> >>> Selection.NumberFormat = "m/d/yy h:mm;@"
> >>>
> >>> Columns("B:B").Select
> >>>
> >>> Selection.NumberFormat = "0.00"
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> Dim rng As Range
> >>>
> >>> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
> >>>
> >>> ActiveSheet.Cells(1, 1).End(xlDown))
> >>>
> >>> rng.Offset(0, 3).Value = 80
> >>>
> >>> rng.Offset(0, 4).Value = 90
> >>>
> >>> Columns("D:D").Select
> >>>
> >>> Selection.NumberFormat = "0.00"
> >>>
> >>> Columns("E:E").Select
> >>>
> >>> Selection.NumberFormat = "0.00"
> >>>
> >>>
> >>>
> >>>' 91 and "f" is to thicken up the bar
> >>>
> >>> rng.Offset(0, 5).Value = 91
> >>>
> >>> Columns("F:F").Select
> >>>
> >>> Selection.NumberFormat = "0.00"
> >>>
> >>>' 2 lines below: move into cell K1, the last cell of a variable column
> >>>length A
> >>>
> >>> Range("K1").Select
> >>>
> >>> ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
> >>>
> >>>' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
> >>>length)
> >>>
> >>> Range("K2").Select
> >>>
> >>> ActiveCell.Formula = "=mid(K1, 1,10)"
> >>>
> >>>' 5 lines copy cell k2 to k3, then drop /es
> >>>
> >>> Range("K2").Select
> >>>
> >>> Selection.Copy
> >>>
> >>> Range("K3").Select
> >>>
> >>> ActiveSheet.Paste
> >>>
> >>> ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
> >>>
> >>>
> >>>
> >>> Range("G1").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
> >>>
> >>> Range("G2").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "avg"
> >>>
> >>>
> >>>
> >>> Range("H1").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
> >>>
> >>> Range("H2").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "med"
> >>>
> >>>
> >>>
> >>> Range("I1").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "=max(C[-7])"
> >>>
> >>> Range("I2").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "max"
> >>>
> >>>
> >>>
> >>> Range("I3").Select
> >>>
> >>> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
> >>>
> >>> Range("I4").Select
> >>>
> >>> ActiveCell.FormulaR1C1 = "whenmax"
> >>>
> >>>
> >>>
> >>> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
> >>>
> >>> Range("E1").Activate
> >>>
> >>> Charts.Add
> >>>
> >>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
> >>>Column"
> >>>
> >>>' 2 lines below changed into variable cells with code below these 2
> >>>
> >>>' ActiveChart.SetSourceData
> >>>Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
> >>>
> >>>' :=xlColumns
> >>>
> >>>Dim myrange As Range
> >>>
> >>>Set myrange =
> >>>Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
> >>>
> >>> Sheets("sel60minsweek").Range("e1").End(xlDown))
> >>>
> >>>ActiveChart.SetSourceData Source:=myrange, _
> >>>
> >>> PlotBy:=xlColumns
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ActiveChart.Location Where:=xlLocationAsNewSheet
> >>>
> >>> With ActiveChart
> >>>
> >>> .HasTitle = True
> >>>
> >>>' was below with mm/dd/yy and got replcaed with k2 cell for date
> >>>
> >>>' .ChartTitle.Characters.Text = _
> >>>
> >>>' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
> >>>AVERAGE% WEEKLY MEDIAN% "
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> .ChartTitle.Characters.Text = _
> >>>
> >>>"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
> >>>TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
> >>> HIGHEST HOURLY CPU
> >>>ENDING " & Worksheets(1).Range("i3").Value & " " &
> >>>Worksheets(1).Range("i1") & " %"
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> .Axes(xlCategory, xlPrimary).HasTitle = True
> >>>
> >>> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
> >>>
> >>> "ENDING HOUR TIME"
> >>>
> >>> .Axes(xlValue, xlPrimary).HasTitle = True
> >>>
> >>> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
> >>>
> >>> .Axes(xlCategory, xlSecondary).HasTitle = False
> >>>
> >>> .Axes(xlValue, xlSecondary).HasTitle = False
> >>>
> >>> End With
> >>>
> >>> ActiveChart.Legend.Select
> >>>
> >>> Selection.Delete
> >>>
> >>> ActiveChart.SeriesCollection(1).Select
> >>>
> >>> With Selection.Border
> >>>
> >>> .Weight = xlThin
> >>>
> >>> .LineStyle = xlAutomatic
> >>>
> >>> End With
> >>>
> >>> Selection.Shadow = False
> >>>
> >>> Selection.InvertIfNegative = False
> >>>
> >>> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
> >>>
> >>> Variant:=2, _
> >>>
> >>> Degree:=0.231372549019608
> >>>
> >>> With Selection
> >>>
> >>> .Fill.Visible = True
> >>>
> >>> .Fill.ForeColor.SchemeColor = 50
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>' With Selection.Interior
> >>>
> >>>' .ColorIndex = 43
> >>>
> >>>' .Pattern = xlSolid
> >>>
> >>> End With
> >>>
> >>> ActiveChart.SeriesCollection(3).Select
> >>>
> >>> With Selection.Border
> >>>
> >>> .ColorIndex = 57
> >>>
> >>> .Weight = xlThick
> >>>
> >>> .LineStyle = xlContinuous
> >>>
> >>> End With
> >>>
> >>> With Selection
> >>>
> >>> .MarkerBackgroundColorIndex = xlAutomatic
> >>>
> >>> .MarkerForegroundColorIndex = xlAutomatic
> >>>
> >>> .MarkerStyle = xlNone
> >>>
> >>> .Smooth = False
> >>>
> >>> .MarkerSize = 9
> >>>
> >>> .Shadow = False
> >>>
> >>> End With
> >>>
> >>> ActiveChart.SeriesCollection(4).Select
> >>>
> >>> With Selection.Border
> >>>
> >>> .ColorIndex = 3
> >>>
> >>> .Weight = xlThick
> >>>
> >>> .LineStyle = xlContinuous
> >>>
> >>> End With
> >>>
> >>> With Selection
> >>>
> >>> .MarkerBackgroundColorIndex = xlNone
> >>>
> >>> .MarkerForegroundColorIndex = xlAutomatic
> >>>
> >>> .MarkerStyle = xlNone
> >>>
> >>> .Smooth = False
> >>>
> >>> .MarkerSize = 5
> >>>
> >>> .Shadow = False
> >>>
> >>> End With
> >>>
> >>> ActiveChart.PlotArea.Select
> >>>
> >>> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
> >>>
> >>> .Select
> >>>
> >>> .AutoSize = True
> >>>
> >>> .Formula = "=sel60minsweek!$G$1"
> >>>
> >>> End With
> >>>
> >>> Selection.ShapeRange.IncrementLeft -11.44
> >>>
> >>> Selection.ShapeRange.IncrementTop -203.49
> >>>
> >>> ActiveChart.PlotArea.Select
> >>>
> >>> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
> >>>
> >>> .Select
> >>>
> >>> .AutoSize = True
> >>>
> >>> .Formula = "=sel60minsweek!$H$1"
> >>>
> >>> End With
> >>>
> >>> Selection.ShapeRange.IncrementLeft 104.2
> >>>
> >>> Selection.ShapeRange.IncrementTop -203.49
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> With ActiveChart.PageSetup
> >>>
> >>> .LeftHeader = ""
> >>>
> >>> .CenterHeader = ""
> >>>
> >>> .RightHeader = ""
> >>>
> >>> .LeftFooter = ""
> >>>
> >>> .CenterFooter = ""
> >>>
> >>> .RightFooter = ""
> >>>
> >>> .LeftMargin = Application.InchesToPoints(0.75)
> >>>
> >>> .RightMargin = Application.InchesToPoints(0.75)
> >>>
> >>> .TopMargin = Application.InchesToPoints(1)
> >>>
> >>> .BottomMargin = Application.InchesToPoints(1)
> >>>
> >>> .HeaderMargin = Application.InchesToPoints(0.5)
> >>>
> >>> .FooterMargin = Application.InchesToPoints(0.5)
> >>>
> >>> .ChartSize = xlFullPage
> >>>
> >>> .PrintQuality = 600
> >>>
> >>> .CenterHorizontally = False
> >>>
> >>> .CenterVertically = False
> >>>
> >>> .Orientation = xlLandscape
> >>>
> >>> .Draft = False
> >>>
> >>> .PaperSize = xlPaperLetter
> >>>
> >>> .FirstPageNumber = xlAutomatic
> >>>
> >>> .BlackAndWhite = False
> >>>
> >>> .Zoom = 100
> >>>
> >>> End With
> >>>
> >>> ActiveChart.Deselect
> >>>
> >>>
> >>>
> >>>' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
> >>>
> >>>' " & Worksheets(1).Range("k2").Value & "
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> ChDir "H:\MY DOCUMENTS ON H DRIVE"
> >>>
> >>> ActiveWorkbook.SaveAs Filename:= _
> >>>
> >>>"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
> >>>& ".xls", FileFormat:=xlNormal, _
> >>>
> >>> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> >>>
> >>> CreateBackup:=False
> >>>
> >>>End Sub
> >>>
> >>>
> >>>
> >>
>
>

Bob -

To make a combination chart, add the two series to the chart as more
column series. Then select one, and using Chart Type on the Chart menu,
change it to a line style chart. Select the other series and repeat (the
F4 key is the shortcut for Repeat Last Action).

If the macro knows how many cells are being charted, it should know the
range containing those cells. So it should be able to determine what to
use for the range in place of B$2:B$20. And it should know which cells
to fill in with the formulas in place of C2:C20 and D2:D20.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

BOB-THE-K wrote:
> Followed your code and I got the columns with MAX as a different color.
> That's great. One more go at this and I think I'll have it. Remember, I
> originally showed that this was a "LINES AND COLUMNS" chart. COL A is
> date/timeinfo COL B is cpu% used COL C is blank COL D is filled with all 80s
> (to make a 80% threshhold line, COL E all 90 for 90%).
>
>
> With the chart you had me build, how do I get those lines (80 90) back?
> 2nd and final... my current charting is done in a macro already, with COL A
> and COL B having variable no. of cells. How do I tweek those "MAX" formulas
> for variable. Once again, thank you for your time. I probably will be able to
> complete my task after the above 2 questions have been answered.
>
> "Jon Peltier" wrote:
>
>
>>Bob -
>>
>>Your data looks a little funny, but here goes.
>>
>>Supposing A2:A20 has your X categories, and B2:B20 has your values for the column
>>series. Keep A1 blank, put a label in B1. Insert two columns after B, and put the
>>labels "Green" and "Max" in the new C1 and D1. In C2 enter this formula:
>>
>>=IF(B2<MAX(B$2:B$20),B2,0)
>>
>>and fill down to C20 with this. In D2 enter this formula:
>>
>>=IF(B2=MAX(B$2:B$20),B2,0)
>>
>>and fill this down to D20. Your maximum will show up in column D, and all others
>>will be in C.
>>
>>Instead of plotting column B, plot both C and D, as either stacked columns, or as
>>clustered columns with an overlap of 100% (Overlap is on the Format Series dialog,
>>Options tab). Color the two series differently, then embellish with all the rest of
>>your lines.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>>The conditional sure looks like where I'd like to be, but I can't quite grasp
>>>how to set for the 1 highest entry in COL B", and not ranges as are shown in
>>>the example..
>>>
>>>
>>>
>>>HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
>>>column I'd like to light up. Any more clues are appreciated.
>>>A B c D E
>>>02/21/200509.59.00 10.27 80 90
>>>
>>> I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
>>>So wherever 65 shows up in the chart, that's what I'd like different
>>>colored. 65 by itself is not a range, I can't see how to adapt to your
>>>example. The 80 and 90 are percentage max lines left to right, while col b
>>>gets turned to up/down bars on the graph. Thanks again for whatever you
>>>give.
>>>
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>Bob -
>>>>
>>>>Maybe you could adapt the following conditional charting technique, by
>>>>moving the maximum value to its own series which you've formatted with
>>>>the other color:
>>>>
>>>> http://peltiertech.com/Excel/Charts/...nalChart1.html
>>>>
>>>>This page may also give you an idea:
>>>>
>>>> http://peltiertech.com/Excel/Charts/FormatMinMax.html
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>BOB-THE-K wrote:
>>>>
>>>>
>>>>
>>>>>I have the code below. Green bars and 80% yellow line and 90% red line. You
>>>>>will also see I have the "max" value. I'd like that max column to be other
>>>>>than the set green for all the bars. I set the colors near the end of the
>>>>>program. I've looked at some of the examples at the support site, but I'm not
>>>>>sure if any fits me. Thanks.
>>>>>C O D E B E L O W...............
>>>>>Sub Macro1()
>>>>>
>>>>>'
>>>>>
>>>>>' Macro1 Macro
>>>>>
>>>>>' Macro recorded 3/12/2004 by bkondos
>>>>>
>>>>>'
>>>>>
>>>>>
>>>>>
>>>>>' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
>>>>>
>>>>> ChDir "H:\"
>>>>>
>>>>> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
>>>>>StartRow:=1, _
>>>>>
>>>>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
>>>>>ConsecutiveDelimiter _
>>>>>
>>>>> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
>>>>>
>>>>> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
>>>>>
>>>>> TrailingMinusNumbers:=True
>>>>>
>>>>> Columns("A:A").Select
>>>>>
>>>>> Selection.NumberFormat = "m/d/yy h:mm;@"
>>>>>
>>>>> Columns("B:B").Select
>>>>>
>>>>> Selection.NumberFormat = "0.00"
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Dim rng As Range
>>>>>
>>>>> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
>>>>>
>>>>> ActiveSheet.Cells(1, 1).End(xlDown))
>>>>>
>>>>> rng.Offset(0, 3).Value = 80
>>>>>
>>>>> rng.Offset(0, 4).Value = 90
>>>>>
>>>>> Columns("D:D").Select
>>>>>
>>>>> Selection.NumberFormat = "0.00"
>>>>>
>>>>> Columns("E:E").Select
>>>>>
>>>>> Selection.NumberFormat = "0.00"
>>>>>
>>>>>
>>>>>
>>>>>' 91 and "f" is to thicken up the bar
>>>>>
>>>>> rng.Offset(0, 5).Value = 91
>>>>>
>>>>> Columns("F:F").Select
>>>>>
>>>>> Selection.NumberFormat = "0.00"
>>>>>
>>>>>' 2 lines below: move into cell K1, the last cell of a variable column
>>>>>length A
>>>>>
>>>>> Range("K1").Select
>>>>>
>>>>>ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
>>>>>
>>>>>' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
>>>>>length)
>>>>>
>>>>>Range("K2").Select
>>>>>
>>>>>ActiveCell.Formula = "=mid(K1, 1,10)"
>>>>>
>>>>>' 5 lines copy cell k2 to k3, then drop /es
>>>>>
>>>>>Range("K2").Select
>>>>>
>>>>>Selection.Copy
>>>>>
>>>>>Range("K3").Select
>>>>>
>>>>>ActiveSheet.Paste
>>>>>
>>>>>ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
>>>>>
>>>>>
>>>>>
>>>>> Range("G1").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
>>>>>
>>>>> Range("G2").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "avg"
>>>>>
>>>>>
>>>>>
>>>>> Range("H1").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
>>>>>
>>>>> Range("H2").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "med"
>>>>>
>>>>>
>>>>>
>>>>> Range("I1").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "=max(C[-7])"
>>>>>
>>>>> Range("I2").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "max"
>>>>>
>>>>>
>>>>>
>>>>> Range("I3").Select
>>>>>
>>>>> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
>>>>>
>>>>> Range("I4").Select
>>>>>
>>>>> ActiveCell.FormulaR1C1 = "whenmax"
>>>>>
>>>>>
>>>>>
>>>>> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
>>>>>
>>>>> Range("E1").Activate
>>>>>
>>>>> Charts.Add
>>>>>
>>>>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
>>>>>Column"
>>>>>
>>>>>' 2 lines below changed into variable cells with code below these 2
>>>>>
>>>>>' ActiveChart.SetSourceData
>>>>>Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
>>>>>
>>>>>' :=xlColumns
>>>>>
>>>>>Dim myrange As Range
>>>>>
>>>>>Set myrange =
>>>>>Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
>>>>>
>>>>> Sheets("sel60minsweek").Range("e1").End(xlDown))
>>>>>
>>>>>ActiveChart.SetSourceData Source:=myrange, _
>>>>>
>>>>>PlotBy:=xlColumns
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ActiveChart.Location Where:=xlLocationAsNewSheet
>>>>>
>>>>> With ActiveChart
>>>>>
>>>>> .HasTitle = True
>>>>>
>>>>>' was below with mm/dd/yy and got replcaed with k2 cell for date
>>>>>
>>>>>' .ChartTitle.Characters.Text = _
>>>>>
>>>>>' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
>>>>>AVERAGE% WEEKLY MEDIAN% "
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> .ChartTitle.Characters.Text = _
>>>>>
>>>>>"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
>>>>>TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
>>>>> HIGHEST HOURLY CPU
>>>>>ENDING " & Worksheets(1).Range("i3").Value & " " &
>>>>>Worksheets(1).Range("i1") & " %"
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> .Axes(xlCategory, xlPrimary).HasTitle = True
>>>>>
>>>>> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
>>>>>
>>>>> "ENDING HOUR TIME"
>>>>>
>>>>> .Axes(xlValue, xlPrimary).HasTitle = True
>>>>>
>>>>> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
>>>>>
>>>>> .Axes(xlCategory, xlSecondary).HasTitle = False
>>>>>
>>>>> .Axes(xlValue, xlSecondary).HasTitle = False
>>>>>
>>>>> End With
>>>>>
>>>>> ActiveChart.Legend.Select
>>>>>
>>>>> Selection.Delete
>>>>>
>>>>> ActiveChart.SeriesCollection(1).Select
>>>>>
>>>>> With Selection.Border
>>>>>
>>>>> .Weight = xlThin
>>>>>
>>>>> .LineStyle = xlAutomatic
>>>>>
>>>>> End With
>>>>>
>>>>> Selection.Shadow = False
>>>>>
>>>>> Selection.InvertIfNegative = False
>>>>>
>>>>> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
>>>>>
>>>>> Variant:=2, _
>>>>>
>>>>> Degree:=0.231372549019608
>>>>>
>>>>> With Selection
>>>>>
>>>>> .Fill.Visible = True
>>>>>
>>>>> .Fill.ForeColor.SchemeColor = 50
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>' With Selection.Interior
>>>>>
>>>>>' .ColorIndex = 43
>>>>>
>>>>>' .Pattern = xlSolid
>>>>>
>>>>> End With
>>>>>
>>>>> ActiveChart.SeriesCollection(3).Select
>>>>>
>>>>> With Selection.Border
>>>>>
>>>>> .ColorIndex = 57
>>>>>
>>>>> .Weight = xlThick
>>>>>
>>>>> .LineStyle = xlContinuous
>>>>>
>>>>> End With
>>>>>
>>>>> With Selection
>>>>>
>>>>> .MarkerBackgroundColorIndex = xlAutomatic
>>>>>
>>>>> .MarkerForegroundColorIndex = xlAutomatic
>>>>>
>>>>> .MarkerStyle = xlNone
>>>>>
>>>>> .Smooth = False
>>>>>
>>>>> .MarkerSize = 9
>>>>>
>>>>> .Shadow = False
>>>>>
>>>>> End With
>>>>>
>>>>> ActiveChart.SeriesCollection(4).Select
>>>>>
>>>>> With Selection.Border
>>>>>
>>>>> .ColorIndex = 3
>>>>>
>>>>> .Weight = xlThick
>>>>>
>>>>> .LineStyle = xlContinuous
>>>>>
>>>>> End With
>>>>>
>>>>> With Selection
>>>>>
>>>>> .MarkerBackgroundColorIndex = xlNone
>>>>>
>>>>> .MarkerForegroundColorIndex = xlAutomatic
>>>>>
>>>>> .MarkerStyle = xlNone
>>>>>
>>>>> .Smooth = False
>>>>>
>>>>> .MarkerSize = 5
>>>>>
>>>>> .Shadow = False
>>>>>
>>>>> End With
>>>>>
>>>>> ActiveChart.PlotArea.Select
>>>>>
>>>>> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
>>>>>
>>>>> .Select
>>>>>
>>>>> .AutoSize = True
>>>>>
>>>>> .Formula = "=sel60minsweek!$G$1"
>>>>>
>>>>> End With
>>>>>
>>>>> Selection.ShapeRange.IncrementLeft -11.44
>>>>>
>>>>> Selection.ShapeRange.IncrementTop -203.49
>>>>>
>>>>> ActiveChart.PlotArea.Select
>>>>>
>>>>> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
>>>>>
>>>>> .Select
>>>>>
>>>>> .AutoSize = True
>>>>>
>>>>> .Formula = "=sel60minsweek!$H$1"
>>>>>
>>>>> End With
>>>>>
>>>>> Selection.ShapeRange.IncrementLeft 104.2
>>>>>
>>>>> Selection.ShapeRange.IncrementTop -203.49
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> With ActiveChart.PageSetup
>>>>>
>>>>> .LeftHeader = ""
>>>>>
>>>>> .CenterHeader = ""
>>>>>
>>>>> .RightHeader = ""
>>>>>
>>>>> .LeftFooter = ""
>>>>>
>>>>> .CenterFooter = ""
>>>>>
>>>>> .RightFooter = ""
>>>>>
>>>>> .LeftMargin = Application.InchesToPoints(0.75)
>>>>>
>>>>> .RightMargin = Application.InchesToPoints(0.75)
>>>>>
>>>>> .TopMargin = Application.InchesToPoints(1)
>>>>>
>>>>> .BottomMargin = Application.InchesToPoints(1)
>>>>>
>>>>> .HeaderMargin = Application.InchesToPoints(0.5)
>>>>>
>>>>> .FooterMargin = Application.InchesToPoints(0.5)
>>>>>
>>>>> .ChartSize = xlFullPage
>>>>>
>>>>> .PrintQuality = 600
>>>>>
>>>>> .CenterHorizontally = False
>>>>>
>>>>> .CenterVertically = False
>>>>>
>>>>> .Orientation = xlLandscape
>>>>>
>>>>> .Draft = False
>>>>>
>>>>> .PaperSize = xlPaperLetter
>>>>>
>>>>> .FirstPageNumber = xlAutomatic
>>>>>
>>>>> .BlackAndWhite = False
>>>>>
>>>>> .Zoom = 100
>>>>>
>>>>> End With
>>>>>
>>>>> ActiveChart.Deselect
>>>>>
>>>>>
>>>>>
>>>>>' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
>>>>>
>>>>>' " & Worksheets(1).Range("k2").Value & "
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ChDir "H:\MY DOCUMENTS ON H DRIVE"
>>>>>
>>>>> ActiveWorkbook.SaveAs Filename:= _
>>>>>
>>>>>"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
>>>>>& ".xls", FileFormat:=xlNormal, _
>>>>>
>>>>> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
>>>>>
>>>>> CreateBackup:=False
>>>>>
>>>>>End Sub
>>>>>
>>>>>
>>>>>
>>>>
>>

Just wanted so say thanks again. I looked at your examples and tried a few
times, and it finally rang the bell as what to do. The combo charting was new
to me, so your time and examples were very appreciated.


"Jon Peltier" wrote:

> Bob -
>
> To make a combination chart, add the two series to the chart as more
> column series. Then select one, and using Chart Type on the Chart menu,
> change it to a line style chart. Select the other series and repeat (the
> F4 key is the shortcut for Repeat Last Action).
>
> If the macro knows how many cells are being charted, it should know the
> range containing those cells. So it should be able to determine what to
> use for the range in place of B$2:B$20. And it should know which cells
> to fill in with the formulas in place of C2:C20 and D2:D20.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> BOB-THE-K wrote:
> > Followed your code and I got the columns with MAX as a different color.
> > That's great. One more go at this and I think I'll have it. Remember, I
> > originally showed that this was a "LINES AND COLUMNS" chart. COL A is
> > date/timeinfo COL B is cpu% used COL C is blank COL D is filled with all 80s
> > (to make a 80% threshhold line, COL E all 90 for 90%).
> >
> >
> > With the chart you had me build, how do I get those lines (80 90) back?
> > 2nd and final... my current charting is done in a macro already, with COL A
> > and COL B having variable no. of cells. How do I tweek those "MAX" formulas
> > for variable. Once again, thank you for your time. I probably will be able to
> > complete my task after the above 2 questions have been answered.
> >
> > "Jon Peltier" wrote:
> >
> >
> >>Bob -
> >>
> >>Your data looks a little funny, but here goes.
> >>
> >>Supposing A2:A20 has your X categories, and B2:B20 has your values for the column
> >>series. Keep A1 blank, put a label in B1. Insert two columns after B, and put the
> >>labels "Green" and "Max" in the new C1 and D1. In C2 enter this formula:
> >>
> >>=IF(B2<MAX(B$2:B$20),B2,0)
> >>
> >>and fill down to C20 with this. In D2 enter this formula:
> >>
> >>=IF(B2=MAX(B$2:B$20),B2,0)
> >>
> >>and fill this down to D20. Your maximum will show up in column D, and all others
> >>will be in C.
> >>
> >>Instead of plotting column B, plot both C and D, as either stacked columns, or as
> >>clustered columns with an overlap of 100% (Overlap is on the Format Series dialog,
> >>Options tab). Color the two series differently, then embellish with all the rest of
> >>your lines.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>>The conditional sure looks like where I'd like to be, but I can't quite grasp
> >>>how to set for the 1 highest entry in COL B", and not ranges as are shown in
> >>>the example..
> >>>
> >>>
> >>>
> >>>HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
> >>>column I'd like to light up. Any more clues are appreciated.
> >>>A B c D E
> >>>02/21/200509.59.00 10.27 80 90
> >>>
> >>> I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
> >>>So wherever 65 shows up in the chart, that's what I'd like different
> >>>colored. 65 by itself is not a range, I can't see how to adapt to your
> >>>example. The 80 and 90 are percentage max lines left to right, while col b
> >>>gets turned to up/down bars on the graph. Thanks again for whatever you
> >>>give.
> >>>
> >>>
> >>>"Jon Peltier" wrote:
> >>>
> >>>
> >>>
> >>>>Bob -
> >>>>
> >>>>Maybe you could adapt the following conditional charting technique, by
> >>>>moving the maximum value to its own series which you've formatted with
> >>>>the other color:
> >>>>
> >>>> http://peltiertech.com/Excel/Charts/...nalChart1.html
> >>>>
> >>>>This page may also give you an idea:
> >>>>
> >>>> http://peltiertech.com/Excel/Charts/FormatMinMax.html
> >>>>
> >>>>- Jon
> >>>>-------
> >>>>Jon Peltier, Microsoft Excel MVP
> >>>>Peltier Technical Services
> >>>>Tutorials and Custom Solutions
> >>>>http://PeltierTech.com/
> >>>>_______
> >>>>
> >>>>BOB-THE-K wrote:
> >>>>
> >>>>
> >>>>
> >>>>>I have the code below. Green bars and 80% yellow line and 90% red line. You
> >>>>>will also see I have the "max" value. I'd like that max column to be other
> >>>>>than the set green for all the bars. I set the colors near the end of the
> >>>>>program. I've looked at some of the examples at the support site, but I'm not
> >>>>>sure if any fits me. Thanks.
> >>>>>C O D E B E L O W...............
> >>>>>Sub Macro1()
> >>>>>
> >>>>>'
> >>>>>
> >>>>>' Macro1 Macro
> >>>>>
> >>>>>' Macro recorded 3/12/2004 by bkondos
> >>>>>
> >>>>>'
> >>>>>
> >>>>>
> >>>>>
> >>>>>' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
> >>>>>
> >>>>> ChDir "H:\"
> >>>>>
> >>>>> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
> >>>>>StartRow:=1, _
> >>>>>
> >>>>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
> >>>>>ConsecutiveDelimiter _
> >>>>>
> >>>>> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
> >>>>>
> >>>>> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
> >>>>>
> >>>>> TrailingMinusNumbers:=True
> >>>>>
> >>>>> Columns("A:A").Select
> >>>>>
> >>>>> Selection.NumberFormat = "m/d/yy h:mm;@"
> >>>>>
> >>>>> Columns("B:B").Select
> >>>>>
> >>>>> Selection.NumberFormat = "0.00"
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> Dim rng As Range
> >>>>>
> >>>>> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
> >>>>>
> >>>>> ActiveSheet.Cells(1, 1).End(xlDown))
> >>>>>
> >>>>> rng.Offset(0, 3).Value = 80
> >>>>>
> >>>>> rng.Offset(0, 4).Value = 90
> >>>>>
> >>>>> Columns("D:D").Select
> >>>>>
> >>>>> Selection.NumberFormat = "0.00"
> >>>>>
> >>>>> Columns("E:E").Select
> >>>>>
> >>>>> Selection.NumberFormat = "0.00"
> >>>>>
> >>>>>
> >>>>>
> >>>>>' 91 and "f" is to thicken up the bar
> >>>>>
> >>>>> rng.Offset(0, 5).Value = 91
> >>>>>
> >>>>> Columns("F:F").Select
> >>>>>
> >>>>> Selection.NumberFormat = "0.00"
> >>>>>
> >>>>>' 2 lines below: move into cell K1, the last cell of a variable column
> >>>>>length A
> >>>>>
> >>>>> Range("K1").Select
> >>>>>
> >>>>>ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
> >>>>>
> >>>>>' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
> >>>>>length)
> >>>>>
> >>>>>Range("K2").Select
> >>>>>
> >>>>>ActiveCell.Formula = "=mid(K1, 1,10)"
> >>>>>
> >>>>>' 5 lines copy cell k2 to k3, then drop /es
> >>>>>
> >>>>>Range("K2").Select
> >>>>>
> >>>>>Selection.Copy
> >>>>>
> >>>>>Range("K3").Select
> >>>>>
> >>>>>ActiveSheet.Paste
> >>>>>
> >>>>>ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
> >>>>>
> >>>>>
> >>>>>
> >>>>> Range("G1").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
> >>>>>
> >>>>> Range("G2").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "avg"
> >>>>>
> >>>>>
> >>>>>
> >>>>> Range("H1").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
> >>>>>
> >>>>> Range("H2").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "med"
> >>>>>
> >>>>>
> >>>>>
> >>>>> Range("I1").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "=max(C[-7])"
> >>>>>
> >>>>> Range("I2").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "max"
> >>>>>
> >>>>>
> >>>>>
> >>>>> Range("I3").Select
> >>>>>
> >>>>> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
> >>>>>
> >>>>> Range("I4").Select
> >>>>>
> >>>>> ActiveCell.FormulaR1C1 = "whenmax"
> >>>>>
> >>>>>
> >>>>>
> >>>>> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
> >>>>>
> >>>>> Range("E1").Activate
> >>>>>
> >>>>> Charts.Add
> >>>>>
> >>>>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
> >>>>>Column"
> >>>>>
> >>>>>' 2 lines below changed into variable cells with code below these 2
> >>>>>
> >>>>>' ActiveChart.SetSourceData
> >>>>>Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
> >>>>>
> >>>>>' :=xlColumns
> >>>>>
> >>>>>Dim myrange As Range
> >>>>>
> >>>>>Set myrange =
> >>>>>Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
> >>>>>
> >>>>> Sheets("sel60minsweek").Range("e1").End(xlDown))
> >>>>>
> >>>>>ActiveChart.SetSourceData Source:=myrange, _
> >>>>>
> >>>>>PlotBy:=xlColumns
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> ActiveChart.Location Where:=xlLocationAsNewSheet
> >>>>>
> >>>>> With ActiveChart
> >>>>>
> >>>>> .HasTitle = True
> >>>>>
> >>>>>' was below with mm/dd/yy and got replcaed with k2 cell for date
> >>>>>
> >>>>>' .ChartTitle.Characters.Text = _
> >>>>>
> >>>>>' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
> >>>>>AVERAGE% WEEKLY MEDIAN% "
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> .ChartTitle.Characters.Text = _
> >>>>>
> >>>>>"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
> >>>>>TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
> >>>>> HIGHEST HOURLY CPU
> >>>>>ENDING " & Worksheets(1).Range("i3").Value & " " &
> >>>>>Worksheets(1).Range("i1") & " %"
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> .Axes(xlCategory, xlPrimary).HasTitle = True
> >>>>>
> >>>>> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
> >>>>>
> >>>>> "ENDING HOUR TIME"
> >>>>>
> >>>>> .Axes(xlValue, xlPrimary).HasTitle = True
> >>>>>
> >>>>> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
> >>>>>
> >>>>> .Axes(xlCategory, xlSecondary).HasTitle = False
> >>>>>
> >>>>> .Axes(xlValue, xlSecondary).HasTitle = False
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> ActiveChart.Legend.Select
> >>>>>
> >>>>> Selection.Delete
> >>>>>
> >>>>> ActiveChart.SeriesCollection(1).Select
> >>>>>
> >>>>> With Selection.Border
> >>>>>
> >>>>> .Weight = xlThin
> >>>>>
> >>>>> .LineStyle = xlAutomatic
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> Selection.Shadow = False
> >>>>>
> >>>>> Selection.InvertIfNegative = False
> >>>>>
> >>>>> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
> >>>>>
> >>>>> Variant:=2, _
> >>>>>
> >>>>> Degree:=0.231372549019608
> >>>>>
> >>>>> With Selection
> >>>>>
> >>>>> .Fill.Visible = True
> >>>>>
> >>>>> .Fill.ForeColor.SchemeColor = 50
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>' With Selection.Interior
> >>>>>
> >>>>>' .ColorIndex = 43
> >>>>>
> >>>>>' .Pattern = xlSolid
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> ActiveChart.SeriesCollection(3).Select
> >>>>>
> >>>>> With Selection.Border
> >>>>>
> >>>>> .ColorIndex = 57
> >>>>>
> >>>>> .Weight = xlThick
> >>>>>
> >>>>> .LineStyle = xlContinuous
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> With Selection
> >>>>>
> >>>>> .MarkerBackgroundColorIndex = xlAutomatic
> >>>>>
> >>>>> .MarkerForegroundColorIndex = xlAutomatic
> >>>>>
> >>>>> .MarkerStyle = xlNone
> >>>>>
> >>>>> .Smooth = False
> >>>>>
> >>>>> .MarkerSize = 9
> >>>>>
> >>>>> .Shadow = False
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> ActiveChart.SeriesCollection(4).Select
> >>>>>
> >>>>> With Selection.Border
> >>>>>
> >>>>> .ColorIndex = 3
> >>>>>
> >>>>> .Weight = xlThick
> >>>>>
> >>>>> .LineStyle = xlContinuous
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> With Selection
> >>>>>
> >>>>> .MarkerBackgroundColorIndex = xlNone
> >>>>>
> >>>>> .MarkerForegroundColorIndex = xlAutomatic
> >>>>>
> >>>>> .MarkerStyle = xlNone
> >>>>>
> >>>>> .Smooth = False
> >>>>>
> >>>>> .MarkerSize = 5
> >>>>>
> >>>>> .Shadow = False
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> ActiveChart.PlotArea.Select
> >>>>>
> >>>>> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
> >>>>>
> >>>>> .Select
> >>>>>
> >>>>> .AutoSize = True
> >>>>>
> >>>>> .Formula = "=sel60minsweek!$G$1"
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> Selection.ShapeRange.IncrementLeft -11.44
> >>>>>
> >>>>> Selection.ShapeRange.IncrementTop -203.49
> >>>>>
> >>>>> ActiveChart.PlotArea.Select
> >>>>>
> >>>>> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
> >>>>>
> >>>>> .Select
> >>>>>
> >>>>> .AutoSize = True
> >>>>>
> >>>>> .Formula = "=sel60minsweek!$H$1"
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> Selection.ShapeRange.IncrementLeft 104.2
> >>>>>
> >>>>> Selection.ShapeRange.IncrementTop -203.49
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> With ActiveChart.PageSetup
> >>>>>
> >>>>> .LeftHeader = ""
> >>>>>
> >>>>> .CenterHeader = ""
> >>>>>
> >>>>> .RightHeader = ""
> >>>>>
> >>>>> .LeftFooter = ""
> >>>>>
> >>>>> .CenterFooter = ""
> >>>>>
> >>>>> .RightFooter = ""
> >>>>>
> >>>>> .LeftMargin = Application.InchesToPoints(0.75)
> >>>>>
> >>>>> .RightMargin = Application.InchesToPoints(0.75)
> >>>>>
> >>>>> .TopMargin = Application.InchesToPoints(1)
> >>>>>
> >>>>> .BottomMargin = Application.InchesToPoints(1)
> >>>>>
> >>>>> .HeaderMargin = Application.InchesToPoints(0.5)
> >>>>>
> >>>>> .FooterMargin = Application.InchesToPoints(0.5)
> >>>>>
> >>>>> .ChartSize = xlFullPage
> >>>>>
> >>>>> .PrintQuality = 600
> >>>>>
> >>>>> .CenterHorizontally = False
> >>>>>
> >>>>> .CenterVertically = False
> >>>>>
> >>>>> .Orientation = xlLandscape
> >>>>>
> >>>>> .Draft = False
> >>>>>
> >>>>> .PaperSize = xlPaperLetter
> >>>>>
> >>>>> .FirstPageNumber = xlAutomatic
> >>>>>
> >>>>> .BlackAndWhite = False
> >>>>>
> >>>>> .Zoom = 100
> >>>>>
> >>>>> End With
> >>>>>
> >>>>> ActiveChart.Deselect
> >>>>>
> >>>>>
> >>>>>
> >>>>>' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
> >>>>>
> >>>>>' " & Worksheets(1).Range("k2").Value & "
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> ChDir "H:\MY DOCUMENTS ON H DRIVE"
> >>>>>
> >>>>> ActiveWorkbook.SaveAs Filename:= _
> >>>>>
> >>>>>"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
> >>>>>& ".xls", FileFormat:=xlNormal, _
> >>>>>
> >>>>> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
> >>>>>
> >>>>> CreateBackup:=False
> >>>>>
> >>>>>End Sub
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>
>

Bob -

Glad to help.

- Jon

BOB-THE-K wrote:

> Just wanted so say thanks again. I looked at your examples and tried a few
> times, and it finally rang the bell as what to do. The combo charting was new
> to me, so your time and examples were very appreciated.
>
>
> "Jon Peltier" wrote:
>
>
>>Bob -
>>
>>To make a combination chart, add the two series to the chart as more
>>column series. Then select one, and using Chart Type on the Chart menu,
>>change it to a line style chart. Select the other series and repeat (the
>>F4 key is the shortcut for Repeat Last Action).
>>
>>If the macro knows how many cells are being charted, it should know the
>>range containing those cells. So it should be able to determine what to
>>use for the range in place of B$2:B$20. And it should know which cells
>>to fill in with the formulas in place of C2:C20 and D2:D20.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>BOB-THE-K wrote:
>>
>>>Followed your code and I got the columns with MAX as a different color.
>>>That's great. One more go at this and I think I'll have it. Remember, I
>>>originally showed that this was a "LINES AND COLUMNS" chart. COL A is
>>>date/timeinfo COL B is cpu% used COL C is blank COL D is filled with all 80s
>>>(to make a 80% threshhold line, COL E all 90 for 90%).
>>>
>>>
>>>With the chart you had me build, how do I get those lines (80 90) back?
>>>2nd and final... my current charting is done in a macro already, with COL A
>>>and COL B having variable no. of cells. How do I tweek those "MAX" formulas
>>>for variable. Once again, thank you for your time. I probably will be able to
>>>complete my task after the above 2 questions have been answered.
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>Bob -
>>>>
>>>>Your data looks a little funny, but here goes.
>>>>
>>>>Supposing A2:A20 has your X categories, and B2:B20 has your values for the column
>>>>series. Keep A1 blank, put a label in B1. Insert two columns after B, and put the
>>>>labels "Green" and "Max" in the new C1 and D1. In C2 enter this formula:
>>>>
>>>>=IF(B2<MAX(B$2:B$20),B2,0)
>>>>
>>>>and fill down to C20 with this. In D2 enter this formula:
>>>>
>>>>=IF(B2=MAX(B$2:B$20),B2,0)
>>>>
>>>>and fill this down to D20. Your maximum will show up in column D, and all others
>>>>will be in C.
>>>>
>>>>Instead of plotting column B, plot both C and D, as either stacked columns, or as
>>>>clustered columns with an overlap of 100% (Overlap is on the Format Series dialog,
>>>>Options tab). Color the two series differently, then embellish with all the rest of
>>>>your lines.
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>
>>>>
>>>>>The conditional sure looks like where I'd like to be, but I can't quite grasp
>>>>>how to set for the 1 highest entry in COL B", and not ranges as are shown in
>>>>>the example..
>>>>>
>>>>>
>>>>>
>>>>>HERE's partial spreadsheet data.. the highest in COLB is 65.95. That's the
>>>>>column I'd like to light up. Any more clues are appreciated.
>>>>>A B c D E
>>>>>02/21/200509.59.00 10.27 80 90
>>>>>
>>>>>I have 44 in G1 (avg) 50 in H1 (median) and 65 in I1 (max)..
>>>>>So wherever 65 shows up in the chart, that's what I'd like different
>>>>>colored. 65 by itself is not a range, I can't see how to adapt to your
>>>>>example. The 80 and 90 are percentage max lines left to right, while col b
>>>>>gets turned to up/down bars on the graph. Thanks again for whatever you
>>>>>give.
>>>>>
>>>>>
>>>>>"Jon Peltier" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Bob -
>>>>>>
>>>>>>Maybe you could adapt the following conditional charting technique, by
>>>>>>moving the maximum value to its own series which you've formatted with
>>>>>>the other color:
>>>>>>
>>>>>> http://peltiertech.com/Excel/Charts/...nalChart1.html
>>>>>>
>>>>>>This page may also give you an idea:
>>>>>>
>>>>>> http://peltiertech.com/Excel/Charts/FormatMinMax.html
>>>>>>
>>>>>>- Jon
>>>>>>-------
>>>>>>Jon Peltier, Microsoft Excel MVP
>>>>>>Peltier Technical Services
>>>>>>Tutorials and Custom Solutions
>>>>>>http://PeltierTech.com/
>>>>>>_______
>>>>>>
>>>>>>BOB-THE-K wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>I have the code below. Green bars and 80% yellow line and 90% red line. You
>>>>>>>will also see I have the "max" value. I'd like that max column to be other
>>>>>>>than the set green for all the bars. I set the colors near the end of the
>>>>>>>program. I've looked at some of the examples at the support site, but I'm not
>>>>>>>sure if any fits me. Thanks.
>>>>>>>C O D E B E L O W...............
>>>>>>>Sub Macro1()
>>>>>>>
>>>>>>>'
>>>>>>>
>>>>>>>' Macro1 Macro
>>>>>>>
>>>>>>>' Macro recorded 3/12/2004 by bkondos
>>>>>>>
>>>>>>>'
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>' 60 min avg cpu but weekly 8 hr days for 5 days<<<<<<<<<<<<<<<
>>>>>>>
>>>>>>> ChDir "H:\"
>>>>>>>
>>>>>>> Workbooks.OpenText Filename:="H:\sel60minsweek.txt", Origin:=437,
>>>>>>>StartRow:=1, _
>>>>>>>
>>>>>>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
>>>>>>>ConsecutiveDelimiter _
>>>>>>>
>>>>>>> :=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _
>>>>>>>
>>>>>>> Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
>>>>>>>
>>>>>>> TrailingMinusNumbers:=True
>>>>>>>
>>>>>>> Columns("A:A").Select
>>>>>>>
>>>>>>> Selection.NumberFormat = "m/d/yy h:mm;@"
>>>>>>>
>>>>>>> Columns("B:B").Select
>>>>>>>
>>>>>>> Selection.NumberFormat = "0.00"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Dim rng As Range
>>>>>>>
>>>>>>> Set rng = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
>>>>>>>
>>>>>>> ActiveSheet.Cells(1, 1).End(xlDown))
>>>>>>>
>>>>>>> rng.Offset(0, 3).Value = 80
>>>>>>>
>>>>>>> rng.Offset(0, 4).Value = 90
>>>>>>>
>>>>>>> Columns("D:D").Select
>>>>>>>
>>>>>>> Selection.NumberFormat = "0.00"
>>>>>>>
>>>>>>> Columns("E:E").Select
>>>>>>>
>>>>>>> Selection.NumberFormat = "0.00"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>' 91 and "f" is to thicken up the bar
>>>>>>>
>>>>>>> rng.Offset(0, 5).Value = 91
>>>>>>>
>>>>>>> Columns("F:F").Select
>>>>>>>
>>>>>>> Selection.NumberFormat = "0.00"
>>>>>>>
>>>>>>>' 2 lines below: move into cell K1, the last cell of a variable column
>>>>>>>length A
>>>>>>>
>>>>>>>Range("K1").Select
>>>>>>>
>>>>>>>ActiveCell.Formula = "=offset($A$1,counta(A:A)-1,0)"
>>>>>>>
>>>>>>>' 2 lines below: move to cell K2, only the 1st 8 bytes of K1 (startpos and
>>>>>>>length)
>>>>>>>
>>>>>>>Range("K2").Select
>>>>>>>
>>>>>>>ActiveCell.Formula = "=mid(K1, 1,10)"
>>>>>>>
>>>>>>>' 5 lines copy cell k2 to k3, then drop /es
>>>>>>>
>>>>>>>Range("K2").Select
>>>>>>>
>>>>>>>Selection.Copy
>>>>>>>
>>>>>>>Range("K3").Select
>>>>>>>
>>>>>>>ActiveSheet.Paste
>>>>>>>
>>>>>>>ActiveCell.Value = Replace(ActiveCell.Value, "/", "")
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Range("G1").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "=AVERAGE(C[-5])"
>>>>>>>
>>>>>>> Range("G2").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "avg"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Range("H1").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "=MEDIAN(C[-6])"
>>>>>>>
>>>>>>> Range("H2").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "med"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Range("I1").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "=max(C[-7])"
>>>>>>>
>>>>>>> Range("I2").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "max"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Range("I3").Select
>>>>>>>
>>>>>>> ActiveCell.Formula = "=INDEX(A:A,MATCH(MAX(B:B),B:B,0))"
>>>>>>>
>>>>>>> Range("I4").Select
>>>>>>>
>>>>>>> ActiveCell.FormulaR1C1 = "whenmax"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Range("H2,A:A,B:B,C:C,D:D,E:E").Select
>>>>>>>
>>>>>>> Range("E1").Activate
>>>>>>>
>>>>>>> Charts.Add
>>>>>>>
>>>>>>> ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
>>>>>>>Column"
>>>>>>>
>>>>>>>' 2 lines below changed into variable cells with code below these 2
>>>>>>>
>>>>>>>' ActiveChart.SetSourceData
>>>>>>>Source:=Sheets("sel60minsweek").Range("A1:E20"), PlotBy _
>>>>>>>
>>>>>>>' :=xlColumns
>>>>>>>
>>>>>>>Dim myrange As Range
>>>>>>>
>>>>>>>Set myrange =
>>>>>>>Sheets("sel60minsweek").Range(Sheets("sel60minsweek").Range("a1"), _
>>>>>>>
>>>>>>> Sheets("sel60minsweek").Range("e1").End(xlDown))
>>>>>>>
>>>>>>>ActiveChart.SetSourceData Source:=myrange, _
>>>>>>>
>>>>>>>PlotBy:=xlColumns
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> ActiveChart.Location Where:=xlLocationAsNewSheet
>>>>>>>
>>>>>>> With ActiveChart
>>>>>>>
>>>>>>> .HasTitle = True
>>>>>>>
>>>>>>>' was below with mm/dd/yy and got replcaed with k2 cell for date
>>>>>>>
>>>>>>>' .ChartTitle.Characters.Text = _
>>>>>>>
>>>>>>>' "W.E MM/DD/YY MVSA HOURLY CPU BUSY FROM 9AM TO 5PM " & Chr(10) & "WEEKLY
>>>>>>>AVERAGE% WEEKLY MEDIAN% "
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> .ChartTitle.Characters.Text = _
>>>>>>>
>>>>>>>"W.E " & Worksheets(1).Range("k2").Value & " MVSA HOURLY CPU BUSY FROM 9AM
>>>>>>>TO 5PM " & Chr(10) & " WEEKLY AVERAGE% WEEKLY MEDIAN%
>>>>>>> HIGHEST HOURLY CPU
>>>>>>>ENDING " & Worksheets(1).Range("i3").Value & " " &
>>>>>>>Worksheets(1).Range("i1") & " %"
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> .Axes(xlCategory, xlPrimary).HasTitle = True
>>>>>>>
>>>>>>> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
>>>>>>>
>>>>>>> "ENDING HOUR TIME"
>>>>>>>
>>>>>>> .Axes(xlValue, xlPrimary).HasTitle = True
>>>>>>>
>>>>>>> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PERCENT"
>>>>>>>
>>>>>>> .Axes(xlCategory, xlSecondary).HasTitle = False
>>>>>>>
>>>>>>> .Axes(xlValue, xlSecondary).HasTitle = False
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> ActiveChart.Legend.Select
>>>>>>>
>>>>>>> Selection.Delete
>>>>>>>
>>>>>>> ActiveChart.SeriesCollection(1).Select
>>>>>>>
>>>>>>> With Selection.Border
>>>>>>>
>>>>>>> .Weight = xlThin
>>>>>>>
>>>>>>> .LineStyle = xlAutomatic
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> Selection.Shadow = False
>>>>>>>
>>>>>>> Selection.InvertIfNegative = False
>>>>>>>
>>>>>>> Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, _
>>>>>>>
>>>>>>> Variant:=2, _
>>>>>>>
>>>>>>> Degree:=0.231372549019608
>>>>>>>
>>>>>>> With Selection
>>>>>>>
>>>>>>> .Fill.Visible = True
>>>>>>>
>>>>>>> .Fill.ForeColor.SchemeColor = 50
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>' With Selection.Interior
>>>>>>>
>>>>>>>' .ColorIndex = 43
>>>>>>>
>>>>>>>' .Pattern = xlSolid
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> ActiveChart.SeriesCollection(3).Select
>>>>>>>
>>>>>>> With Selection.Border
>>>>>>>
>>>>>>> .ColorIndex = 57
>>>>>>>
>>>>>>> .Weight = xlThick
>>>>>>>
>>>>>>> .LineStyle = xlContinuous
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> With Selection
>>>>>>>
>>>>>>> .MarkerBackgroundColorIndex = xlAutomatic
>>>>>>>
>>>>>>> .MarkerForegroundColorIndex = xlAutomatic
>>>>>>>
>>>>>>> .MarkerStyle = xlNone
>>>>>>>
>>>>>>> .Smooth = False
>>>>>>>
>>>>>>> .MarkerSize = 9
>>>>>>>
>>>>>>> .Shadow = False
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> ActiveChart.SeriesCollection(4).Select
>>>>>>>
>>>>>>> With Selection.Border
>>>>>>>
>>>>>>> .ColorIndex = 3
>>>>>>>
>>>>>>> .Weight = xlThick
>>>>>>>
>>>>>>> .LineStyle = xlContinuous
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> With Selection
>>>>>>>
>>>>>>> .MarkerBackgroundColorIndex = xlNone
>>>>>>>
>>>>>>> .MarkerForegroundColorIndex = xlAutomatic
>>>>>>>
>>>>>>> .MarkerStyle = xlNone
>>>>>>>
>>>>>>> .Smooth = False
>>>>>>>
>>>>>>> .MarkerSize = 5
>>>>>>>
>>>>>>> .Shadow = False
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> ActiveChart.PlotArea.Select
>>>>>>>
>>>>>>> With ActiveChart.TextBoxes.Add(337, 230, 48, 18)
>>>>>>>
>>>>>>> .Select
>>>>>>>
>>>>>>> .AutoSize = True
>>>>>>>
>>>>>>> .Formula = "=sel60minsweek!$G$1"
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> Selection.ShapeRange.IncrementLeft -11.44
>>>>>>>
>>>>>>> Selection.ShapeRange.IncrementTop -203.49
>>>>>>>
>>>>>>> ActiveChart.PlotArea.Select
>>>>>>>
>>>>>>> With ActiveChart.TextBoxes.Add(387, 230, 48, 18)
>>>>>>>
>>>>>>> .Select
>>>>>>>
>>>>>>> .AutoSize = True
>>>>>>>
>>>>>>> .Formula = "=sel60minsweek!$H$1"
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> Selection.ShapeRange.IncrementLeft 104.2
>>>>>>>
>>>>>>> Selection.ShapeRange.IncrementTop -203.49
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> With ActiveChart.PageSetup
>>>>>>>
>>>>>>> .LeftHeader = ""
>>>>>>>
>>>>>>> .CenterHeader = ""
>>>>>>>
>>>>>>> .RightHeader = ""
>>>>>>>
>>>>>>> .LeftFooter = ""
>>>>>>>
>>>>>>> .CenterFooter = ""
>>>>>>>
>>>>>>> .RightFooter = ""
>>>>>>>
>>>>>>> .LeftMargin = Application.InchesToPoints(0.75)
>>>>>>>
>>>>>>> .RightMargin = Application.InchesToPoints(0.75)
>>>>>>>
>>>>>>> .TopMargin = Application.InchesToPoints(1)
>>>>>>>
>>>>>>> .BottomMargin = Application.InchesToPoints(1)
>>>>>>>
>>>>>>> .HeaderMargin = Application.InchesToPoints(0.5)
>>>>>>>
>>>>>>> .FooterMargin = Application.InchesToPoints(0.5)
>>>>>>>
>>>>>>> .ChartSize = xlFullPage
>>>>>>>
>>>>>>> .PrintQuality = 600
>>>>>>>
>>>>>>> .CenterHorizontally = False
>>>>>>>
>>>>>>> .CenterVertically = False
>>>>>>>
>>>>>>> .Orientation = xlLandscape
>>>>>>>
>>>>>>> .Draft = False
>>>>>>>
>>>>>>> .PaperSize = xlPaperLetter
>>>>>>>
>>>>>>> .FirstPageNumber = xlAutomatic
>>>>>>>
>>>>>>> .BlackAndWhite = False
>>>>>>>
>>>>>>> .Zoom = 100
>>>>>>>
>>>>>>> End With
>>>>>>>
>>>>>>> ActiveChart.Deselect
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>' "H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E.MMDDYY", FileFormat:=xlNormal, _
>>>>>>>
>>>>>>>' " & Worksheets(1).Range("k2").Value & "
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> ChDir "H:\MY DOCUMENTS ON H DRIVE"
>>>>>>>
>>>>>>> ActiveWorkbook.SaveAs Filename:= _
>>>>>>>
>>>>>>>"H:\MY DOCUMENTS ON H DRIVE\WEEKLYCPUW.E." & Worksheets(1).Range("k3").Value
>>>>>>>& ".xls", FileFormat:=xlNormal, _
>>>>>>>
>>>>>>> Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
>>>>>>>
>>>>>>> CreateBackup:=False
>>>>>>>
>>>>>>>End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>