VBA to Randomly Highlight a specific number of Cells in each row

[Flexcel22]

Hello Mr and Mrs Excel,

I had a range Range("A1:I18")

I wanted to randomly highlight 5 cells from each row within this range.

For example Highlight 5 cells from Row A1 to I1
5 Row A2 to I2
5 Row A3 to I3
,, ,, ,,
Randomly until the last row which is A18 to I18

Thanks in advance

Any Ideas??

 

[mrshl9898]

Re: VBA toRandomly Highlight a specific number of Cells in each row

How often do you want the random highlight to occur? Each row, 5 cells per row?

Is it truly random (so may be identical) or does it need to be different each time?

 

[Rick Rothstein]

Re: VBA toRandomly Highlight a specific number of Cells in each row

I had a range Range("A1:I18")

I wanted to randomly highlight 5 cells from each row within this range.

For example Highlight 5 cells from Row A1 to I1
5 Row A2 to I2
5 Row A3 to I3
,, ,, ,,
Randomly until the last row which is A18 to I18

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightFiveRandomeCellsPerRowA1ToI18()
  Dim R As Long, Cnt As Long, RandomIndex As Long, Temp As Variant
  Static Arr As Variant
  Randomize
  For R = 1 To 18
    If IsEmpty(Arr) Then Arr = [{1,2,3,4,5,6,7,8,9}]
    Cells(R, 1).Resize(, 9).Interior.Color = xlNone
    For Cnt = 9 To 1 Step -1
      RandomIndex = Int(Cnt * Rnd) + 1
      Temp = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(Cnt)
      Arr(Cnt) = Temp
      If Cnt > 4 Then Cells(R, Temp).Interior.Color = RGB(255, 204, 153)
    Next
  Next
End Sub[/td]
[/tr]
[/table]

 

[Flexcel22]

Re: VBA toRandomly Highlight a specific number of Cells in each row

Hi Rick Rothstein,

Thanks for the code. It works like charm. Exactly the way I wanted.

If I may, Could you please point out my range in your code? Like If I wanted to change my range later on.

Sorry for inquiring more but I would appreciate if you could do some incubating as I'm actually a new egg in vba.

 

[Rick Rothstein]

Re: VBA toRandomly Highlight a specific number of Cells in each row

If I may, Could you please point out my range in your code? Like If I wanted to change my range later on.

You should have mentioned that the fixed range you specified was not absolutely fixed an could change as that changes the code quite a bit. Here is the revised code to work in any range you specify (change the red highlighted text) with as many cells to highlight as you specify (change the green highlighted text)...

Sub HighlightFiveRandomeCellsPerRowA1ToI18()
  Dim R As Long, Cnt As Long, RandomIndex As Long, RandomCount As Long
  Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long
  Dim Rng As Range, Temp As Variant
  Static Arr As Variant
  RandomCount =[B][COLOR="#008000"][SIZE=4]5[/SIZE][/COLOR][/B]
  Set Rng = Range("[B][COLOR="#FF0000"]A1:I18[/COLOR][/B]")
  FirstRow = Rng.Rows(1).Row
  LastRow = Split(Rng.Address(1, 0), "$")(2)
  FirstCol = Rng.Columns(1).Column
  LastCol = Rng(Rng.Columns.Count).Column
  Randomize
  For R = FirstRow To LastRow
    If IsEmpty(Arr) Then Arr = Evaluate("ROW(" & FirstCol & ":" & LastCol & ")")
    Intersect(Rows(R), Rng).Interior.Color = xlNone
    For Cnt = UBound(Arr) To 1 Step -1
      RandomIndex = Int(Cnt * Rnd) + 1
      Temp = Arr(RandomIndex, 1)
      Arr(RandomIndex, 1) = Arr(Cnt, 1)
      Arr(Cnt, 1) = Temp
      If Cnt > UBound(Arr) - RandomCount Then
        Cells(R, Temp).Interior.Color = RGB(255, 204, 153)
      End If
    Next
  Next
End Sub

 

[Flexcel22]

Re: VBA toRandomly Highlight a specific number of Cells in each row

Hi Again Rick Rothstein,

The last code did the same thing as your first one but thankfully, this last one is much flexible in the way that I could change the range as desired.

Thanks a million Rothstein and have a wonderful and blessed day

 

[Rick Rothstein]

Re: VBA toRandomly Highlight a specific number of Cells in each row

Hi Again Rick Rothstein,

The last code did the same thing as your first one but thankfully, this last one is much flexible in the way that I could change the range as desired.

In case you missed it in my comments, you can also change the number of cells to highlight on a single row by changing the value assigned to the RandomCount variable.

Thanks a million Rothstein and have a wonderful and blessed day

You are quite welcome... I am glad I was able to help.