• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying Something Different

G

guitarman

Member
Hi All

I would like to create a sheet were you can get the cells to light up and stop on a number after say 3secs or 5secs so the light flashes across all the cells and then stops and then you restart it to get the another number. These numbers are members so there are 250 members so there will be 250 cells and it will stop say on member 95 and then it will flash around again and stop say on member 187. This will be transmitted to the Bar on a large screen TV so members can see the draws being made. But can this be done as a Formula, or is it a macro, or is it a written programme I am unsure. Any help would be very much appreciated. Many Thanks
 
Put the numbers in cells arranged however you like

Take a note of the top left corner , assume D5

In a cell say C1 put a random number generator

=Randbetween(1, 250)


Now select the cells with the numbers on them

Add a Conditional Format

Use a Formula

=D5=$C$1

Apply a bright format

Apply


Now every time you want a new member simply press F9
 
Hi Hui

Thanks for your quick reply but I do not fully understand.I have filled out 250 cells with members and that is from A1-J25 and have took note of A1 which in this case is 44. Do I put =Randbetween(1, 250) in cell K1? and put the format =A1=$K$1 in all 250 cells.And please excuse my ignorance what is a bright format you see I am obviously not in your league when it comes to these things. Many Thanks
 
Good day guitarman


I have uploaded a workbook with Huis formula, I have expanded it slightly. Columns CDIJ are hidden.


https://dl.dropboxusercontent.com/u/75495784/Rand%20colour.xlsx


I have cut the range down to 30 names, you want 250 names so I assume you already have

them and can adjust the ranges to suit. (no way am I doing 250)


Just do as Hui pointed out and press F9 for a new name
 
Hi Mike ,


If you want that the cells should light up with a particular colour in a circular fashion , then I think VBA is the way to go ; what Hui has posted will just select a random cell from the given range , each time you press F9.


Narayan
 
This is neat. I've been playing with this, but, I noticed that eventually, it stops at a single name. Why is that? Does the RANDBETWEEN function not allow the same number twice or something?
 
Good day 3G


If you mean the spread sheet I have F9 for over 5 minuets and it has not stopped on a name, and if you hold down F9 you get a quick scroll effect and then release the key to stop on a random name, that effect may fulfill guitarman's requirements and look good on a large screen
 
Hi, 3G!

b(ut)ob(ut)hc suggestion about holding down F9 requires a huge amount of energy. Regarding becoming more green and ecologic friendly, you might use a toothpick instead.

Regards!

PS: providing that the toothpick is made with recycled elements.
 
Good day Sir JB7


Instead of a tooth pick I was thinking of a drinking bird (the Simpsons) Y/N/Y/N/Y/N/Y/N/Y/N/Y/N
 
Good morning Gentlemen

I think I have probably not explained correctly what I want to do. I have 250 cells with a different name in each one which are the members. All I want to do is have a flashing light go from cell to cell and stop after 3secs and then resume with another selection I think Narayank911 has got the idea of what I need but putting it into practice is another matter.I saw this on a TV show a bit ago and thought at the time what a good idea that is to select the pairings for the summer medals or is it TV trickery?
 
Narayank991

My sincere apologies in my previous post I referred to you as Narayank911 a slip of the typing finger sorry. Anyway I was saying in that post that you had the idea of what I want to do. And that is have the 250 members names in the 250 cells and have a flashing light go through all the cells and stop on 1 after say 3 secs and then carry on and select another and so on, but I am not very good at macros I was wondering if you could give me some assistance. many thanks

Mike
 
Hi Mike ,


Come on , don't embarrass me ! No need to apologize for these things.


Sure , I can post the macro ; in fact I was thinking of writing it when you posted ; I have just put down a first draft ; hope someone can improve it and take it forward , since I can continue only tomorrow morning.

[pre]
Code: 
Public Sub Circular_Lights()
Const HIGHLIGHT = vbYellow                        '  Change as required

Dim Start_cell As Integer

Dim Data_Range As Range
ThisWorkbook.Worksheets("Sheet1").Activate        '  Change as required
Set Data_Range = ActiveSheet.Range("E7:L13")      '  Change as required

Start_Row = Data_Range.Row
Start_Col = Data_Range.Column
Start_Time = Now

Number_of_Rows = Data_Range.Rows.Count
Number_of_Columns = Data_Range.Columns.Count

Row_Selection = Int(Rnd() * Number_of_Rows)
Col_Selection = Int(Rnd() * Number_of_Columns)
With Data_Range.Cells(1, 1)
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT

Do
DoEvents
Curr_Time = Now
If Curr_Time >= Start_Time + TimeValue("00:00:01") Then
Start_Time = Curr_Time
.Offset(Row_Selection, Col_Selection).Interior.Color = xlNone

Col_Selection = Col_Selection + 1
If Col_Selection >= Number_of_Columns Then
Col_Selection = 0
Row_Selection = Row_Selection + 1
If Row_Selection >= Number_of_Rows Then
Row_Selection = 0
End If
End If
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
End If
Loop
End With
End Sub
[/pre]
Narayan
 
Hi Narayank991

Thanks for that if you have not finished it there is no hurry I will not need it until next week so if you want to finish it tomorrow thats fine. Many Thanks

Mike
 
I've attempted to tweak Narayan's code. Sped up the flashing lights, and setup a different time to determine when to stop.

[pre]
Code: 
Option Explicit
Public Sub Circular_Lights()
Const HIGHLIGHT = vbYellow  '  Change as required

Dim Number_of_Rows As Long
Dim Number_of_Columns As Long
Dim Row_Selection As Long
Dim Col_Selection As Long
Dim Start_Time As Date
Dim Curr_Time As Date
Dim i As Integer

Dim Data_Range As Range
ThisWorkbook.Worksheets("Sheet1").Activate        '  Change as required
Set Data_Range = ActiveSheet.Range("E7:L13")      '  Change as required

Start_Time = Now

Number_of_Rows = Data_Range.Rows.Count
Number_of_Columns = Data_Range.Columns.Count

Row_Selection = Int(Rnd() * Number_of_Rows)
Col_Selection = Int(Rnd() * Number_of_Columns)
Data_Range.ClearFormats
With Data_Range.Cells(1, 1)
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
i = 1
Do
DoEvents
Curr_Time = Now
i = i + 1
'Stop flashing? aka, how long do we do running lights
If Curr_Time >= Start_Time + TimeValue("00:00:03") Then
'We have a winner! Highlight all cells except our winner
Data_Range.Cells.Interior.Color = HIGHLIGHT
.Offset(Row_Selection, Col_Selection).Interior.Color = vbRed

'How long do we wait before resuming?
Application.Wait (Now + TimeValue("00:00:03"))
Data_Range.ClearFormats
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
Start_Time = Now
i = 1

'Keep on flashing
'5000 picked after experiments. Seems to run at a nice speed.
'Increase to slow down, decrease to speed up
ElseIf i = 5000 Then
i = 1

.Offset(Row_Selection, Col_Selection).Interior.Color = xlNone

Col_Selection = Col_Selection + 1
If Col_Selection >= Number_of_Columns Then
Col_Selection = 0
Row_Selection = Row_Selection + 1
If Row_Selection >= Number_of_Rows Then
Row_Selection = 0
End If
End If
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
End If
Loop
End With
End Sub
[/pre]
 
Hi Luke ,


Thanks. I was thinking of doing the following :


1. Provide START and STOP buttons to start the process of highlighting the cells


2. The circulating highlight should stop at random ; the STOP button is only to abort the process.


I also wanted to separate the timer part of the code , since I have seen enough examples on the Internet of timer classes , so that the timer becomes asynchronous and pretty accurate.


Since Mike is not in so much of a hurry , I'll post something by Sunday.


Narayan
 
Hi Narayan


I have been playing about with the Macro with my limited knowledge, and have got it working. I was just wondering if there is a way to alter it to make the light go around the cells in a non uniformed pattern. Like jumping from say cell 1 to cell 28 to cell 126 and then cell 45 and so on. And then that is truly random.

Mike
 
Hi Mike ,


Sure ; we need to increment the row and column selection within the loop in random fashion instead of by 1.


I'll post the changed code.


Narayan
 
Hi Mike ,


Can you see if this is OK ? I have made the minor changes required based on the code Luke has posted.

[pre]
Code: 
Public Sub Circular_Lights()
Const HIGHLIGHT = vbYellow  '  Change as required

Dim Number_of_Rows As Long
Dim Number_of_Columns As Long
Dim Row_Selection As Long
Dim Col_Selection As Long
Dim Start_Time As Date
Dim Curr_Time As Date
Dim i As Integer

Dim Data_Range As Range
ThisWorkbook.Worksheets("Sheet1").Activate        '  Change as required
Set Data_Range = ActiveSheet.Range("E7:L13")      '  Change as required

Start_Time = Now

Number_of_Rows = Data_Range.Rows.Count
Number_of_Columns = Data_Range.Columns.Count

Row_Selection = Int(Rnd() * Number_of_Rows)
Col_Selection = Int(Rnd() * Number_of_Columns)
Data_Range.ClearFormats
With Data_Range.Cells(1, 1)
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
i = 1
Do
DoEvents
Curr_Time = Now
i = i + 1
'Stop flashing? aka, how long do we do running lights
If Curr_Time >= Start_Time + TimeValue("00:00:07") Then
'We have a winner! Highlight all cells except our winner
Data_Range.Cells.Interior.Color = HIGHLIGHT
.Offset(Row_Selection, Col_Selection).Interior.Color = vbRed

'How long do we wait before resuming?
Application.Wait (Now + TimeValue("00:00:03"))

Data_Range.ClearFormats
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
Start_Time = Now
i = 1

'Keep on flashing
'5000 picked after experiments. Seems to run at a nice speed.
'Increase to slow down, decrease to speed up
ElseIf i = 30000 Then
i = 1

.Offset(Row_Selection, Col_Selection).Interior.Color = xlNone

Row_Selection = Int(Rnd() * Number_of_Rows)
Col_Selection = Int(Rnd() * Number_of_Columns)

'Col_Selection = Col_Selection + 1
'If Col_Selection >= Number_of_Columns Then
'   Col_Selection = 0
'   Row_Selection = Row_Selection + 1
'   If Row_Selection >= Number_of_Rows Then
'      Row_Selection = 0
'   End If
'End If
.Offset(Row_Selection, Col_Selection).Interior.Color = HIGHLIGHT
End If
Loop
End With
End Sub
[/pre]
Narayan
 
Hi Narayan

That is absolutely brilliant it works just as I had hoped it would. The members sat in the bar were all well impressed to be able to see the actual draw being made and shown on a big 50inch TV and low and behold there were no moans or groans because they could see the draw was all fair and above board.My many thanks to you and of course Luke because it would not have been possible without the assistance of both of you.I can play golf quite well I play off 3 and I also play the guitar quite well but to be able to do what you guys do I am afraid I am left in your wake. May you both have a good day and a brilliant life. And once again Many Thanks

Mike
 
Aw shucks, we're glad we were able to help. I'll give kudos to Narayan for following through on this. Looked like a fun challenge. Several aspects of this challenge would even make for a good tutorial of sorts...random number generation, highlighting cells, timed events.
 
You must log in or register to reply here.
Back
Top