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

B

BOB-THE-K

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
 
J

Jon Peltier

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/ConditionalChart1.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 said:
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
Click to expand...
 
B

BOB-THE-K

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 said:
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/ConditionalChart1.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 said:
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
Click to expand...
Click to expand...
 
J

Jon Peltier

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.


:

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/ConditionalChart1.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
Click to expand...
Click to expand...
Click to expand...
 
B

BOB-THE-K

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


:

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/ConditionalChart1.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
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

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

:

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.


:



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/ConditionalChart1.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
Click to expand...
Click to expand...
Click to expand...
 
B

BOB-THE-K

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

:

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.


:



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/ConditionalChart1.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
Click to expand...
Click to expand...
Click to expand...
 
J

Jon Peltier

Bob -

Glad to help.

- Jon

BOB-THE-K said:
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.


:

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

:



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.


:




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/ConditionalChart1.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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

reverse axis not working as before... 0
Charts in Multiple Sheet in Excel 1
how to add error bars into bar chart 0
Creating Pivot Table with Macro using Multiple Sheets 0
Code to Create Chart 0
Help on adding the second trendline in a "lines on 2 axes" Chart via VBA 1
add a chart in a Add-In 1
Bay chart error bars automation - constrained to averages? 2

Top