Highlight Cell (Conditional Formatting or VBA Worksheet event) equals cell - BINGO tracker

Hi! I need a vba which highlights cell for numbers, I was hoping you could help me. Two options, either worksheet event or conditional formatting. Basically, at every drawn number user would input it to cell "C1", then an event (or vba) highlights the same from range "B3:P7", cell color (formatting) should be retained until I reset it thru a command button. What would be fancier (wishlist), is for number to be bigger (and/or bolder) ... e.g. when "B4" is drawn it sorts of outweighs others...

Thanks in advance Excel gurus! Appreciate it...

try this, in the worksheet module:

Please Login or Register  to view this content.

Use procedure ResetBingo to reset the grid.

Tried it and works beautifully. I've added data validation for the input to accept 1-75 only... how about a vba to avoid duplication (keying in the same number after it has been drawn already)? Also, if it is not too much to ask, can I have a code wherein after the "RESET" the drawn numbers are stored/ kept into a separate sheet for audit purposes? Again, thank you for the time and effort. Thanks in advance!

Here's amended code to log a list of drawn numbers, for each reset game. You'll need to add a worksheet, and change the sLogSheet variable name to suit. It also checks whether a value has already been drawn.

Please Login or Register  to view this content.

thank you so much Olly for taking time out to assist me. question, the cfound and cdrawn toggles the format settings of the cell by triggering BGON and FONTON... what of I wanted to retain the format after clearing or accepting input at C1... what I mean is another formatting for example for "already" drawn numbers (cFOUND) say "FONTRetained" and "BGRetained" not the default black executed by the vba? another With statement? appreciate the help, i will try the code re logs and will let you know.... again my heartfelt gratitude.

I don't understand what you want?

There are 3 possibilities, when a number is "drawn" (entered in cell C3):

Option 1: The Number is found in the bingo range, and has not already been drawn.
In this case, the most recently drawn number is highlighted with font colour (iFontOn) and interior colour (iBGOn).
All numbers which have preivously been drawn are highlighted with font colour (iFontOff) and interior colour (iBGOn).
The most recently drawn number is added to the bottom of the most recent list of drawn numbers on the log worksheet.


Option 2: The Number is found in the bingo range, and HAS already been drawn.
In this case, no changes are made to the formatting of the bingo grid. The duplicate value is shown in the VBA immediate window.


Option 3: The Number is NOT found in the bingo range
In this case, no changes are made to the formatting of the bingo grid. The unfound value is shown in the VBA immediate window. The application beeps.


I'm not clear what you want to change?