I am trying to create a subroutine that compares the difference between the two tests scores (column B & C) and
highlights the row that show the greatest improvement (MAX) in green, and the worst improvement decline (MIN) in pink. I
want to use a “For Next” loop to get this done. I have the following code but there is an error in my MAX and MIN functions.
Can I get some help to obtain this objective? (See attached workbook.)
I would also like to run the subroutine with a shortcut.
'Shortcut key Alt-B to run
Application.OnKey "%b", "TestDiff"
'Use "For Next" subroutine to find max and min differences between column ranges Test 1 & Test 2'
'Then highlight row (columns A,B,&C) for max value in green & highlight row for min value in pink'
Public Sub TestDiff()
Dim Test1 As Range, Test2 As Range, Diff As Double
Set Test1 = Range("B4:B13")
Set Test2 = Range("C4:C13")
Test1 -Test2 = Diff
For Each Diff In ActiveSheet
=MAX(IF(B4:B13=10,C4:C13)) 'Determines MAX for Diff
=MIN(IF(B4:B13=10,C4:C13)) 'Determines MIN for Diff
'Color cell interior green'
If Cell.Value = Max Then
Cell.Interior.ColorIndex = 4
'Color cell interior pink'
If Cell.Value = Min Then
Cell.Interior.ColorIndex = 7
Name Test #1 Score Test #2 Score
Bob Fenton 67 53
Mary Right 75 89
Gale Hernandez 98 95
Mark Deutsch 89 82
Gary Miles 72 73
Fred Flinstone 99 85
Barry Gillian 83 79
Nancy Aguirre 85 72
Sharon Fox 79 80
Martin Noonan 59 58
** This post is also found cross-posted at MrExcel.com. - Excelforum.com is the original post.
So, please reply to this post in this forum right where you are at right here. (i.e.,
http://www.mrexcel.com/forum/showthr...41#post2936141 ) I will note this for the MrExcel participants to post here in this
forum; and provide them with the link to this exact location. Thank-you.