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("sel60mins week").Range("a1"), _
Sheets("sel60minsweek").Range("e1").End(xlDown))
ActiveChart.SetSourceData Source:=myrange, _
PlotBy:=xlColumns
ActiveChart.Location Whe=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