Random Highlight only 1 in the each row

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

I need a VBA, which can highlight 1 character out of 1X2 in the each row so far each time 14 total are selected within the 14</SPAN></SPAN>

Example..

Book1
	A	B	C	D	E	F	G
1
2
3
4
5
6		1	1	X	2
7		2	1	X	2
8		3	1	X	2
9		4	1	X	2
10		5	1	X	2
11		6	1	X	2
12		7	1	X	2
13		8	1	X	2
14		9	1	X	2
15		10	1	X	2
16		11	1	X	2
17		12	1	X	2
18		13	1	X	2
19		14	1	X	2
20
21
22
23
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Rick Rothstein]

Does this macro do what you want...

Sub RandomHighlight()
  Dim R As Long, LastRow As Long
  Randomize
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C6:E" & LastRow).ClearFormats
  For R = 6 To LastRow
    Cells(R, Int(3 * Rnd + 3)).Interior.Color = 52479
  Next
End Sub

 

[DanteAmor]

Just another way

Sub Test()
  Dim c As Range
  Range("C6:E19").Interior.ColorIndex = xlNone
  For Each c In Range("C6:C19")
    c.Cells(1).Offset(, WorksheetFunction.RandBetween(0, 2)).Interior.Color = 52479
  Next
End Sub

 

[Kishan]

Does this macro do what you want...

Sub RandomHighlight()
  Dim R As Long, LastRow As Long
  Randomize
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C6:E" & LastRow).ClearFormats
  For R = 6 To LastRow
    Cells(R, Int(3 * Rnd + 3)).Interior.Color = 52479
  Next
End Sub

Hi Rick, it worked as wanted, just a question I mean to say selection of 1 random out of 3 could you make it bit like machine game have some move left and right before selection after when 1 is selected go for next selection I am not sure I have explained it well.</SPAN></SPAN>

Thank you very much for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

Just another way

Sub Test()
  Dim c As Range
  Range("C6:E19").Interior.ColorIndex = xlNone
  For Each c In Range("C6:C19")
    c.Cells(1).Offset(, WorksheetFunction.RandBetween(0, 2)).Interior.Color = 52479
  Next
End Sub

DanteAmor, code stuck at line below I thinks it is not compatible with my version</SPAN></SPAN>

c.Cells(1).Offset(, WorksheetFunction.RandBetween(0, 2)).Interior.Color = 52479

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[DanteAmor]

It may be the version.
What version of Excel do you use?

 

[Rick Rothstein]

What version of Excel do you use?

Kishan lists his version at the top of his first messages... he is using XL2000.

 

[Rick Rothstein]

...could you make it bit like machine game have some move left and right before selection after when 1 is selected go for next selection

I cannot get it to move the colors back and forth in any reasonable time frame, but maybe this will be acceptable to you...

Sub RandomHighlight()
  Dim R As Long, X As Long, Z As Long, LastRow As Long
  Randomize
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C6:E" & LastRow).ClearFormats
  For R = 6 To LastRow
    Cells(R, "C").Resize(, 3).ClearFormats
    Cells(R, Int(3 * Rnd + 3)).Interior.Color = 52479
    Application.Wait Now + 0.00001
  Next
End Sub

 

[Tom.Jones]

Does this macro do what you want...

Sub RandomHighlight()
  Dim R As Long, LastRow As Long
  Randomize
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("C6:E" & LastRow).ClearFormats
  For R = 6 To LastRow
    Cells(R, Int(3 * Rnd + 3)).Interior.Color = 52479
  Next
End Sub

Hi,

If Range will be B6:Z30, with some empty cells, can you show me how to modify your code.
Need to highlight 5 or 6 cells
Thank you.

 

[Peter_SSs]

.. could you make it bit like machine game have some move left and right before selection after when 1 is selected go for next selection

See if either of these is what you mean. If so, you can experiment with changing the 'Const' lines near the start of each code to get the desired effect.

Sub Rnd_Highlights_1()
  Dim rw As Range, rng As Range
  Dim i As Long, j As Long, colold As Long, colnew As Long
  
  Const CycleStart As Long = 4
  Const CycleSize As Long = 5
  Const Delay As Long = 50000000
  
  Randomize
  Set rng = Range("C6", Range("E" & Rows.Count).End(xlUp))
  rng.Interior.Color = xlNone
  For Each rw In rng.Rows
    For i = 1 To Int(CycleStart + Rnd() * CycleSize)
      colnew = 1 + Int(Rnd() * 3)
      If colnew <> colold Then
        rw.Interior.Color = xlNone
        colold = colnew
      End If
      rw.Cells(colnew).Interior.Color = 52479
      For j = 1 To Delay: Next j
    Next i
  Next rw
End Sub

Sub Rnd_Highlights_2()
  Dim rw As Range, rng As Range
  Dim i As Long, j As Long, k As Long, colnew As Long
  
  Const CycleStart As Long = 3
  Const CycleSize As Long = 4
  Const Delay As Long = 90000000
  
  Randomize
  Set rng = Range("C6", Range("E" & Rows.Count).End(xlUp))
  rng.Interior.Color = xlNone
  For Each rw In rng.Rows
    For i = 1 To Int(CycleStart + Rnd() * CycleSize)
      For k = 1 To 3
        rw.Cells(k).Interior.Color = 52479
        For j = 1 To Delay: Next j
        rw.Interior.Color = xlNone
      Next k
    Next i
    colnew = 1 + Int(Rnd() * 3)
    rw.Cells(colnew).Interior.Color = 52479
  Next rw
End Sub