I am creating an NCAA Basketball Bracket pool to determine and show who are
the winners of the tournament in the Final Four, the Finals, and lastly,
the National Champion.
What I need is some help in getting the VBA Worksheet_Change Event to work
properly when the Select Winners Per Round macro (found under the Macros
option on the menu bar) is selected and used to populate the winners of
For each winner in the Final Four, the Finals, and lastly, the National
Champion rounds, the names should be highlighted in the ranges, O56:R56,
O57:P57, and O58 and then respectively in the matching names in the range,
For those interested in downloading and seeing the code/situation I am
describing I have uploaded the spreadsheet to my website found here:
I have tried a combination of this code, but cannot get it
Set cellName = Range("X23:X37")
Set cellTest = Range("O56")
colorName = Cells(Application.WorksheetFunction.Match(cellTest,
cellName, 0) _
+ 22, 24).Address(False, False)
' Range("O56").Interior.ColorIndex = 4
Range(colorName).Interior.ColorIndex = 4
It ends up with errors either within the Worksheet_Change event or in
testme() sub. Additionally, another option under Macros on the menu bar,
Select Winners Per Round macro, is the ability to go back to a previous
round to make corrections to the radio/option buttons selected. If this
is the case, then on a name change, the wrong name's ".Interior.ColorIndex"
should be changed to xlNone. This was also causing an error.
Feel free to test out my MsgBox code,* see below, in
'Sub testme()' to see
how the cells don't update until the form is closed.
I am probably pretty close, but I am just missing something
to get it to
' MsgBox "rngO56 = " & Range("O56") & Chr(13) &
"rngX23 = " &
' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then
' MsgBox "boo!"
' Range("X23").Interior.ColorIndex = 4
' MsgBox "who!"
' Range("X23").Interior.ColorIndex = xlNone
' End If
Please let me know if anyone has any questions as needed. I am hoping to
have this finished by Sunday, March 13.