wizardmagu
Board Regular
- Joined
- Dec 27, 2012
- Messages
- 58
- Office Version
- 365
- Platform
- Windows
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
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