I have developed a spreadsheet to track and monitor scores. To the left-side of the spreadsheet there is a Y-axis with a scale ranging from <50 to 100 in increments of 5. My X-axis is along the top of the spreadsheet and contains the values, "Wk1", "Wk2","Wk3",..."Wk30".
I have manually placed "Xs" in the Excel cells to align with the score for a particular week. I have created different color patterns (e.g., 95-100 is blue, 80-90 is green, 65-75 is yellow, 55-60 is orange, and "<50-50" is red). The different color patterns is the reason I want to manually plot the "Xs". However, I do not know how to determine the x-y coordinates of the cells containing the "Xs".
I made a somewhat feeble attempt to record a macro that draws a line between the first two "Xs" so I could see what needed to be done to complete the macro for the entire set of values. In the macro, I highlighted the range of cells containiing the domain (Wk1) and searched for an "X". I went to the next column (Wk2) and searched for "X" again. I then drew a line connecting the two Xs.
I thought I could generalize this and repeat these steps until a line was drawn connecting each X from Wk1 to Wk30. However, I do not know how to determine the coordinates used in the command, ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25). The values represent the first x coordinate, first y coordinate, second x coordinate and second y coordinate. The macro I created is shown below:
Sub TestDraw()
'
' TestDraw Macro
' Macro recorded 2/15/2004 by Art Lawrence
'
ActiveWindow.Visible = False
Windows("Plot Scores.xls").Activate
Range("E9:E20").Select
X1 = Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("F9:F20").Select
X2 = Selection.FindNext(After:=ActiveCell).Activate
ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25).Select
Selection.ShapeRange.Flip msoFlipHorizontal
End Sub
I appreciate any help anyone can give me.
Thanks,
I have manually placed "Xs" in the Excel cells to align with the score for a particular week. I have created different color patterns (e.g., 95-100 is blue, 80-90 is green, 65-75 is yellow, 55-60 is orange, and "<50-50" is red). The different color patterns is the reason I want to manually plot the "Xs". However, I do not know how to determine the x-y coordinates of the cells containing the "Xs".
I made a somewhat feeble attempt to record a macro that draws a line between the first two "Xs" so I could see what needed to be done to complete the macro for the entire set of values. In the macro, I highlighted the range of cells containiing the domain (Wk1) and searched for an "X". I went to the next column (Wk2) and searched for "X" again. I then drew a line connecting the two Xs.
I thought I could generalize this and repeat these steps until a line was drawn connecting each X from Wk1 to Wk30. However, I do not know how to determine the coordinates used in the command, ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25). The values represent the first x coordinate, first y coordinate, second x coordinate and second y coordinate. The macro I created is shown below:
Sub TestDraw()
'
' TestDraw Macro
' Macro recorded 2/15/2004 by Art Lawrence
'
ActiveWindow.Visible = False
Windows("Plot Scores.xls").Activate
Range("E9:E20").Select
X1 = Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("F9:F20").Select
X2 = Selection.FindNext(After:=ActiveCell).Activate
ActiveSheet.Shapes.AddLine(169.5, 215.25, 199.5, 290.25).Select
Selection.ShapeRange.Flip msoFlipHorizontal
End Sub
I appreciate any help anyone can give me.
Thanks,