shuffle, reveal and magnify cell as cell is selected

[Mark2017]

Thank you in advance for any guidance to this difficult request .
I have a list of 70 names in a column.

• I need to randomize this list and hide the names (I'm thinking white font color matching the background).
• Cell A1 would be blank. The starting position.
• Using the Enter key or down arrow key; as focus changes to each selected cell the data would be revealed (font color change) and really magnified pretty much filling the screen.
• As focus changes to the next cell the previous cell would be changed back to "hidden".

In essence, Only a single name is displayed or seen at a time on a large screen and the selected speaker is unknown until the name magically appears on the screen.

I hope this makes sense and again I thank each of you.

Mark

 

[footoo]

Here's a different approach you might want to consider.

Put your randomized name list in B2:B71 and hide column B.
Merge cells C3:J23. Format horizontal alignment to CentreAcrossSelection and vertical alignment to Centre.

Put this in the sheet module :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    [C3].Value = Intersect(Target, rng)(1, 2)
Else
    [C3:J23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
End Sub

 

[footoo]

If you want to randomize the name list each time a selection is made, then :

Merge cells D3:K23 (instead of C3:J23)
In C2:C71 enter =RAND()
Hide columns B & C
Put this in the sheet module :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
Application.ScreenUpdating = False
If Not Intersect(Target, rng) Is Nothing Then
    rng.Offset(0, 1).Resize(, 2).Sort Key1:=[C2], _
        Order1:=xlAscending, Header:=xlNo
    [D3].Value = Intersect(Target, rng)(1, 2)
Else
    [D3:J23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub

 

[Mark2017]

footoo. This solution works great. This is exactly what I was hoping for. Thank you so much.

 

[Mark2017]

If you want to randomize the name list each time a selection is made, then :

footoo, is it possible to randomize the list and then stop without it continuing to randomize each time the selection changes? If so, can this be put into a macro so each meeting it can be randomized and then start right into the display portion? The display part is great

 

[footoo]

Set up like in post # 3 then :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = [A2:A71]
If Target.Cells.Count <> 1 Then Exit Sub
Application.ScreenUpdating = False
If Target.Address = "$A$1" Then
    [D3:K23].ClearContents
    rng.Offset(0, 1).Resize(, 2).Sort Key1:=[C2], _
        Order1:=xlAscending, Header:=xlNo
ElseIf Not Intersect(Target, rng) Is Nothing Then
    [D3].Value = Intersect(Target, rng)(1, 2)
Else
    [D3:K23].ClearContents
End If
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Application.ScreenUpdating = True
End Sub

This will randomize the names only if A1 is selected.