Hi there,
Okay, so I've given this a go, but with an intention of making this a little more worksheet function intensive than VBA.
I did add a VBA routine in the Sheet3 module as follows:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Copies most recent entry to row 21 for later reference
If Not Intersect(Target, Target.Parent.Range("C2:AI17")) Is Nothing Then
Target.Parent.Cells(21, Target.Column).Value = Target.Value
End If
End Sub
But that's as much as I used. If you look in rows 34:42, I re-wrote your table using formulas only. This table is dynamic, and will automatically update as new people get higher/different scores. It uses some Excel rank functions to determine what order the tests belong in, and uses index statements to pull out the appropriate stats after that.
If you like this, you can knock off your entire colour sorting routine. This will probably be much easier for most users to maintain.
In addition, I have to question the need for your CountColors routine. With conditional formatting, you could highlight the answers in your table if they match column B's value. In addition, you could solve column A by using a COUNTIF worksheet function. Again, easier to maintain, and it would also be marginally faster.
If you would like to pursue that, please let me know and I'd be glad to help.
Bookmarks