Get next not empty cell from selected range list with vba

[chris.tina.cy]

I want to create a vba or a command button rule that either highlights a cell from a selected range or shows the selected cell in a different cell.

More specifically, i have 7 names in cells A1:A7. I want everytime I run the code, to get the next available name on the list (either highlighted or shown in a different cell). When the selection reaches A7 which is the last cell, then i want it to go back to cell A1 and start all over again.

What code can i use? And is it better on a vba or on a command button?

Thank you

 

[Gary's Student]

In B1 enter 1 and in C1 enter the formula:

=INDEX(A1:A7,B1)

Then run and re-run this macro:

Sub RoseByAnyName()
    With Range("B1")
        .Value = .Value + 1
        If .Value = 8 Then .Value = 1
    End With
End Sub

 

[chris.tina.cy]

Thank you!