Selecting Random Data
 

I have a spreadsheet that has data in A1:J4163 but the size is not always the
same.
My question: Is there a way that I can make a formula, VBA or Contitional
formating to select rows randomly and color fill the rows

use a cheat row with the formula...
=INT(RAND()*10)+1
where '10' is the range of random #s generated (1-10).
Then create a conditional format that says
Cell Value Is...Equal To...5 (I randomly chose the # 5 for this example :O)
This will only highlight the randomly generated #5 - about 10% of the rows.
Change the '10' to 100 and only about 1% of the rows will have the randomly
generated #5 and therefore meet the conditional formatting criteria.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Willie" wrote:

I have a spreadsheet that has data in A1:J4163 but the size is not always the
same.
My question: Is there a way that I can make a formula, VBA or Contitional
formating to select rows randomly and color fill the rows

For a VBA solution look at the following:

Sub Color_It_Yellow()
Dim How_Many_Rows_Am_I_Using As Long
Dim How_Many_Should_Be_Colored As Long
How_Many_Rows_Am_I_Using = 25
How_Many_Should_Be_Colored = 10

Dim rws(65536) As Long

For i = 1 To How_Many_Should_Be_Colored
rws(i) = Rnd() * How_Many_Rows_Am_I_Using + 1
Rows(rws(i)).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Next i
End Sub
--
Gary's Student


"Gary L Brown" wrote:

use a cheat row with the formula...
=INT(RAND()*10)+1
where '10' is the range of random #s generated (1-10).
Then create a conditional format that says
Cell Value Is...Equal To...5 (I randomly chose the # 5 for this example :O)
This will only highlight the randomly generated #5 - about 10% of the rows.
Change the '10' to 100 and only about 1% of the rows will have the randomly
generated #5 and therefore meet the conditional formatting criteria.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"Willie" wrote:

I have a spreadsheet that has data in A1:J4163 but the size is not always the
same.
My question: Is there a way that I can make a formula, VBA or Contitional
formating to select rows randomly and color fill the rows