Visualize random selection with flashing cells

[LGN]

Hello,

I am playing around with an idea to create a game in Excel, but I know next to nothing about macros and VBA.

I have a set of 7 cells, where one is to be picked at random at the press of a button. This is done with a button and a RandBetween(1,7) macro, which I have successfully created.

However, if the button simply picks the same number several times in succession, it will appear as if nothing happened when the button was pressed, which will be confusing for the player. So I want to insert some kind of visualization between the button click and the resulting number so that the result is intuitively accepted as a fair draw even if the same number is picked several times in a row.

To visualize the randomness, I want the 7 alternative cells to be highlighted (by some kind of format change, border, text color or background color), one at a time, at random order, for a set number of times until the final number is selected. I want this visualization to last for about 1 second.

Can this be done?

Alternatively, I am open for suggestions to other ways to visualize randomness. Maybe a much easier way would be to just have the RandBetween(1,7) refresh 20 or so times before it lands on the final number. And then the corresponding cell can easily be highlighted based on the final value. But how can I make the random number refresh several times? I tried to record a macro where I refresh the function, but it didn't result in the desired effect.

I use Excel 2019.

Edit:
Actually, this can probably be solved by my second suggestion, if there is a conditional format that reads the random number as if refreshes.. Then it should be able to highlight the alternatives until one final alternative is selected?

But I am also going to use that random number to load one out of a set of "cards" (think of this game as Monopoly-style board game). So I don't want the refreshing random number to make the game load a bunch of cards before the final number is selected.
So is there a way to delay the rest of the model for about 1 second until the visualization is complete?

I haven't really created much of this yet, so I don't really have much to attach to this post. Hope the description is sufficient.

[Crooza]

This might get you going.

you can play with the delay in the timer and the colour of the cells

Please Login or Register  to view this content.

[millz]

You can try this version, it moves the "coloured" cell around before slowing down and fixing at the final cell.

Please Login or Register  to view this content.

[LGN]

Thank you both! These are awesome solutions.

Is there a way to reference the final randomly selected cell (not the whole randomization, just the final cell)?

I Tried the CELL("color";A1) function, but it turns out doesn't really reference color at all.

[millz]

Both examples has a reference to the final cell.

Crooza's

Please Login or Register  to view this content.

Mine

Please Login or Register  to view this content.

[LGN]

Both examples has a reference to the final cell.

Maybe I misunderstand something, but how can I make a formula that references the selected cell?

Could you please modify the macro so that for example A4 references the final selection at the end of each draw? Only the final selection, though, not the whole selection process.
(preferably in Crooza's solution, as I think I understood that code better).

[Crooza]

Not sure if you want the cell reference or the number reference. This gives both - the cell in A4 and the number in a5. Modify accordingly.

It also occurred to me that my random number generator may not distribute evenly (numbers 1 and 7 will be 50% less likely to be chosen because of the way I've rounded to select the number). If you want equal distribution maybe look at Millz's code for that.

Please Login or Register  to view this content.