NCAA Basketball Bracket VBA Color Coding for Winners on Worksheet_ChangeEvent...

[Paul B.]

Hello,

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
each game.

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,
O5757, and O58 and then respectively in the matching names in the range,
X23:X37.

For those interested in downloading and seeing the code/situation I am
describing I have uploaded the spreadsheet to my website found here:
http://members.dslextreme.com/users/madforplaid/bracket.xls

I have tried a combination of this code, but cannot get it to work
correctly:

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 the
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
function correctly.

' MsgBox "rngO56 = " & Range("O56") & Chr(13) & "rngX23 = " &
Range("X23")
' If Range("O56").Value = Range("X23").Value Then 'rngO56 = rngX23 Then
' MsgBox "boo!"
' Range("X23").Interior.ColorIndex = 4
' Else
' 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.

Thanks much,

Paul

 

[NickHK]

Paul,
I had a quick look at your WS and whilst I can't immediately give you an
answer to your questions, have you considered using a set of classes and
collections to keep track of your people, teams, round etc.That way, the
class can keep track of it's status (round, won/lost, position, colour index
etc) and when an update is required you just redraw the whole table.
Just an idea.

NickHK

 

[Paul B.]

Nick,

I hadn't thought of doing it the way you suggest, mostly because I am not sure
how to approach it this way.

Would you please show me how it should be written?

Thanks much,

Paul

 

[NickHK]

Paul,
Something like:

Main class (League)
- Property: Year
- Property: Teams (Collection of Team classes. Array of teams names;
read/write if these names can change from year to year)
- Method: OutputStartUp (draw this initial line up, in Excel or to
picture box etc)
- Method: OutputCurrent (draw this current situation, in Excel or to
picture box etc)

Also you would need a collections of:
PersonPlaying class
- Property: BackColour

Team class
- Property: Name
- Property: Holder (A reference to the PersonPlaying class that
"owns" this team)
- Property: CurrentRound
etc.

Not sure it would make sense for you go this route, but then you would not
necessarily be tied to Excel; you just add another option to the
OutputToFormat, as all data is known within the classes.
You would of course have to store the data of the current situation
somehow/where between running of your app.

NickHK

 

[NickHK]

Paul B.,
Found this which may be of interest to you:

http://www.dicks-blog.com/excel/2005 Bracket.zip

NickHK