Display contents of cell in another only when clicked on

Try pasting the following code into the sheet2 tab in the VBA editor (Alt F11)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range

Application.EnableEvents = False
Target.Parent.Unprotect
On Error GoTo NoComments

For Each Cell In Cells.SpecialCells(xlCellTypeComments)
    Cell.Comment.Delete
Next Cell

NoComments:

For Each Cell In Target
    If Cell.Value <> "" Then
        Cell.AddComment
        Cell.Comment.Text Cell.Value
        Cell.Comment.Visible = True
    End If
Next Cell

Target.Parent.Protect
Application.EnableEvents = True

End Sub

Before you protect the worksheet, insert a Comment into each cell into which you want the "big" display. Copy the value of the cell into this Comment. Make sure the Comment box is big enough to display the full text. Then protect the sheet.

Mouse-over D8 in my attachment

It should work if you paste it into the ThisWorkbook tab and use the Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) event instead.

Please try the amendment below

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cell As Range


Select Case Sh.Name
    Case "Rota", "Rota (2)"
        
        Application.EnableEvents = False
        Target.Parent.Unprotect
        On Error GoTo NoComments
        
        For Each Cell In Cells.SpecialCells(xlCellTypeComments)
            Cell.Comment.Delete
        Next Cell
        
NoComments:
        
        For Each Cell In Target
            If Intersect(Range("B6:N12"), Cell) Is Nothing = False Or Intersect(Range("B15:N21"), Cell) Is Nothing = False Then
                If Cell.Value <> "" And Cell.Value <> "D/O" And Cell.Value <> "HOLS" And Cell.Value <> "150" Then
                    If HasComment(Cell) = False Then
                        Cell.AddComment
                    End If
                    Cell.Comment.Text Cell.Value
                    Cell.Comment.Visible = True
                    
                End If
            End If
        Next Cell
        
        Target.Parent.Protect
        Application.EnableEvents = True

End Select

End Sub

Function HasComment(Cell As Range) As Boolean
HasComment = False
On Error GoTo NoCommentFound
X = Cell.Comment.Text
HasComment = True
On Error GoTo 0
Exit Function
NoCommentFound:
End Function