Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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("sel60minswee k").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 -- BOB-THE-K |
#2
|
|||
|
|||
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("sel60minswee k").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 |
#3
|
|||
|
|||
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("sel60minswee k").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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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("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 |
#6
|
|||
|
|||
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("sel60mi nsweek").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 |
#7
|
|||
|
|||
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("sel60mi nsweek").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 |
#8
|
|||
|
|||
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:E 20"), PlotBy _ ' :=xlColumns Dim myrange As Range Set myrange = Sheets("sel60minsweek").Range(Sheets("sel60 minsweek").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
change font color of first cell in data table attached to chart | Charts and Charting in Excel | |||
Prevent change of color scheme in Pivot chart | Charts and Charting in Excel | |||
How do I change the excel chart default plot area color? | Charts and Charting in Excel | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel |