I am trying to place command buttons in the subroutine below for (1) executing the subroutine, and then for (2) clearing the contents of column D. The problem is that the original subroutine has formula =C4-B4 (and copied down) entered into column D for the proper execution of the original subroutine, and a command button to clear column D will erase this formula.
* See 1st attachment without any extra code for command buttons &
** 2nd attachment with my attempts to include the command buttons "Execute" & "Clear"
The subroutine computes the difference in test scores (columns B & C) into column D
'(if =B4-C4 is entered into D4 and copied down each time the subroutine is run)
The subroutine then highlights highest score in green and lowest score in pink.
Public Sub TestDiff()
Dim Test1 As Range, Test2 As Range
Dim dMax As Double, dMin As Double
Dim rCell As Range, rDiff As Range
Set Test1 = Range("B4:B13")
Set Test2 = Range("C4:C13")
'Formula for column D {D4: =C4-B4
copied down to D13}
.
Set rDiff = Range("D4:D13")
dMax = Application.WorksheetFunction.Max(rDiff)
dMin = Application.WorksheetFunction.Min(rDiff)
For Each rCell In rDiff
'Color cell interior green'
If rCell.Value = dMax Then
rCell.Interior.ColorIndex = 4
End If
'Color cell interior pink'
If rCell.Value = dMin Then
rCell.Interior.ColorIndex = 7
End If
Next rCell
End Sub
Name Test #1 Score Test #2 Score Test Score Differences
Bob Fenton 67 53 -14
Mary Right 75 89 14
Gale Hernandez 98 95 -3
Mark Deutsch 89 82 -7
Gary Miles 72 73 1
Fred Flinstone 99 85 -14
Barry Gillian 83 79 -4
Nancy Aguirre 85 72 -13
Sharon Fox 79 80 1
Martin Noonan 59 58 -1
Bookmarks