Highlight random cells within a table

Try such code.

Please Login or Register  to view this content.

I probably don't have to say this, but … just for those not into statistics: Selecting 5% of random cells don't mean they will be evenly distributed in the range - there could be sometimes clusters of few cells (most often - two of course) next to each other and sometimes quite large regions without even single cell highlighted.

If one wants to not allow such clusters, an easy way would be (but then column A and row 1 cannot be included in the selected range) using so called hard core approach - as in the below line

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Of course, in the code above you can use other means of defining the range where highlighting is to be done, like always Range("A1:X100") etc., not just selecting range before running the procedure.

Hi Kaper,

I'm trying to take your code segment a little further and restrict to the non-empty cells only, i.e. not to highlight cells for QC with no data in. If a randomly selected cell is empty, I want the code to skip to the next iteration in the Do loop until a non-empty cell is found. However, running into problems with line 22 (the IF statement after the second ELSE statement), whereby the program breaks should an empty cell be randomly selected. I think this is because no out_range is set if the first cell randomly selected is blank, but unsure how to get the program to move onto the next loop if an error occurs.

Please Login or Register  to view this content.

Thanks,

Rob.

Hi Rob,
Glad you did it and thanks for marking thread solved and for reputation point. Below is a small ammendment to original code which should also work

Please Login or Register  to view this content.