Select A Cell And Format Automatically

[Ark68]

In my worksheet, I have a cell with a default value. It has a specific format as a default value.
With VBA, how could it be written that when the cell is selected (either by code - range("D4").select - for example, or a user clicking on or tabbing to it), the format of that cell is changed and the default value cleared?

 

[DanteAmor]

I didn't understand exactly what you need.
That is, you select cell D4 and you want to clear the contents of the cell and also clear the formatting?

Try this in sheet events.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "D4" Then
    Target.Clear
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Ark68]

Hi Dante, I don't disagree that what I want to do is difficult to explain when my Excel vocabulary and knowledge is weak.
I really appreciate the effort, but in my scenario this wasn't the solution.

I may just live with it so not worried about needing a solution.

But here is a more detailed explanation of the situation just in case someone is looking for a challenge.

• Worksheet "FRONT" is protected, with all cells locked with the exception of D4 and D5.
• D4 is a cell in which the user is to type in their name. Their entry is validated with cell validation (format and length).
• D4 is initially formatted with a blueish italicized text, centered, with a font size of 8. It is basically a prompt for the user as to the data expected ("- Surname, Given - "). This is why the font is so small.
• But the actual value that the user enters should be reformatted to something more readable ... black text, size 11, no italics.
• I can do this after the user has made the change, but before that happens, the user has to type their entry using the difficult to see font.
• What I would like to do, is that when the user starts typing, a more readable font is used, not the default.
• In the "initialization" of my worksheet, I code the selection selection an unlocked cell out of view. This ensures that the user has to click cell D4 in order to type in a value.

Some code associated to this worksheet:

Private Sub Worksheet_AfterUpdate(ByVal Target As Range)
    If Not mbevents Then Exit Sub
    Application.ScreenUpdating = False
    Unprotect
    If Not Intersect(Target, Range("$D$4")) Is Nothing Then
        mbevents = False
        If Range("D4") = "Surname, Given" Then
            With Range("D4")
                .Value = "- Surname, Given -"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            'Stop
            With Range("D4")
                uname = .Value
                .Value = "  " & uname
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
        mbevents = True
        Range("D5").Select
    End If
        
 If Not Intersect(Target, Range("$D$5")) Is Nothing Then
 'Stop
        If Range("D5") = "- Select -" Then
            With Range("D5")
                .Value = "Select"
                .Font.Italic = True
                .Font.Color = RGB(30, 155, 162)
                .Font.Size = 8
            End With
        Else
            With Range("D5")
                wcentre = .Value
                .Font.Italic = False
                .Font.Color = RGB(19, 65, 98)
                .Font.Size = 11
            End With
        End If
        Protect
        Application.ScreenUpdating = True
        Call AddScreenTipTextToColumnC
        'Stop
        Initiate1
    End If

End Sub