Selected Answer
That is quite a task. Install the code below in the code sheet of the worksheet on which you want the action. Then follow the instructions below (and in) the code to adjust it to your needs.
Option Explicit
' set any color you like in a cell and select it.
' in the Immediate window type "? Activecell.Font.Color" (without quotation marks
Const FontColor As Long = 10921638 ' = grey (change as required)
Dim InputCell() As String
Dim InputText() As String
Dim PrevCell As Range
Private Sub Worksheet_Activate()
' 06 Aug 2017
' ==========================================================
' this constant must be set to match the number
' of InputHelp elements you specify below:-
Const NumberOfInputCells As Long = 2
' ==========================================================
Dim InputHelp(NumberOfInputCells) As String
ReDim InputCell(NumberOfInputCells)
ReDim InputText(NumberOfInputCells)
Dim Sp() As String
Dim i As Long
' for each cell with input help specify the
' address and help text, comma separated:-
InputHelp(0) = "A1,Enter your name"
InputHelp(1) = "A4,Enter your birthday"
InputHelp(2) = "A6,Enter your favourite color"
Application.EnableEvents = False
For i = 0 To NumberOfInputCells
Sp = Split(InputHelp(i), ",")
InputCell(i) = Sp(0)
InputText(i) = Sp(1)
With Range(Sp(0))
.Font.Color = FontColor
.HorizontalAlignment = xlLeft
.Value = Sp(1)
End With
Next i
Application.EnableEvents = True
On Error Resume Next
Set PrevCell = ActiveCell
If Err Then
PrevCell = Cells(1, 1)
Err = 0
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 06 Aug 2017
Dim Idx As Long
Application.EnableEvents = False
Idx = Insect(PrevCell)
If Idx > -1 Then
With Range(InputCell(Idx))
If Len(PrevCell.Value) = 0 Then
.Value = InputText(Idx)
.Font.Color = FontColor
End If
End With
End If
Set PrevCell = Target.Cells(1)
Idx = Insect(Target)
If Idx > -1 Then
With Range(InputCell(Idx))
If .Value = InputText(Idx) Then
.Value = ""
.Font.Color = 0
End If
End With
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' 06 Aug 2017
Dim Idx As Long
Idx = Insect(Target)
If Idx > -1 Then
With Target
If (.Value = "") Or (.Value = InputText(Idx)) Then
Application.EnableEvents = False
.Value = InputText(Idx)
.Font.Color = FontColor
Application.EnableEvents = True
End If
End With
End If
End Sub
Private Function Insect(Target As Range) As Long
' 06 Aug 2017
' return the index of InputCells
Dim Rng As Range
Dim i As Long
On Error GoTo ErrHandler
Set Rng = Range(InputCell(0))
For i = 0 To UBound(InputCell)
Set Rng = Application.Union(Rng, Range(InputCell(i)))
Next i
If Application.Intersect(Rng, Target) Is Nothing Then
Insect = -1 ' no match
Else
With Target
For i = 0 To UBound(InputCell)
If .Address = Range(InputCell(i)).Address Then Exit For
Next i
End With
Insect = i
End If
Exit Function
ErrHandler:
If Err = 9 Then
Worksheet_Activate
Err = 0
Resume 0
End If
End Function
It's absolutely essential that you set the constant 'NumberOfInputCells' to the correct value. The "correct value" is the number of 'InputHelp' elements you want to declare. The index of that array starts counting at 0. In the above example the highest count is 2 (meaning 3 cells to have help texts). Therefore 'NumberOfInputCells' = 2.
Note that the default font colour is black (=0). Look for ".Font.Color = 0" in the Selection_Change procedure. That's where you can change it if you want another default colour.
Also note that the input help texts will be updated when you activate a sheet. For your first try, install the code in a blank workbook behind a blank sheet. Activate another sheet and select the first sheet again. You will see the help in A1, A4 and A6.