How to select random names

[wizardmagu]

copied a randomize VBA to randomly select a name in a given range and highlight it green. I then recorded a step to bring the highlighted one to the top. The issue I have is that my # of rows varies and I need it to stop looking dependent on the last row.

I have a button that I assigned this macro to and when I push it it will randomize the list, highlight a name in green and also sort it to the top. Any thoughts on controlling the range?

Sub Pick_Name()
Randomize
Range("A1:A1000").Interior.ColorIndex = xlNone
Cells(Int((80 * Rnd) + 1), 1).Interior.Color = vbGreen
ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort.SortFields.Add(Range( _
"A1"), xlSortOnCellColor, xlAscending, , xlSortTextAsNumbers).SortOnValue. _
Color = RGB(0, 255, 0)
With ActiveWorkbook.Worksheets("Sheet1 (2)").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Fluff]

How about

Sub Pick_Name()

Randomize


With ActiveSheet
   With .Range("A1", Range("A" & Rows.Count).End(xlUp))
      .Interior.ColorIndex = xlNone

      .Cells(Int((.Rows.Count * Rnd) + 1), 1).Interior.Color = vbGreen
   End With
   .AutoFilter.Sort.SortFields.Clear
   .AutoFilter.Sort.SortFields.Add(Range("A1"), xlSortOnCellColor, xlAscending, , xlSortTextAsNumbers).SortOnValue.Color = RGB(0, 255, 0)

    With .AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

 

[wizardmagu]

That worked... Thank you very much. Another question I have is can I add a cell in my excel file "H5" and have the VBA code randomize and highlight that number of cells? For example, if I put in the number 7 into H5 and when I push the button 7 cells are highlighted?

Maybe thats too much but thought I would ask

 

[Fluff]

That would need a new thread, as it's a totally different question.

 

[wizardmagu]

ok gotcha. On that VBA code you supplied if I paste in new information and hit the button to randomize I get an error message. I think its because I would have to manually add a filter. Can I get away from that with a modification to the code?

 

[Fluff]

Yup, just add the line in blue as shown

   End With
   If Not .AutoFilterMode Then .Range("A1").AutoFilter
   .AutoFilter.Sort.SortFields.Clear

 

[wizardmagu]

Thanks for the help!

 

[Fluff]

You're welcome & thanks for the feedback.