Whenever Cell Becomes Empty Then Displays Custom Text

[raindrop]

Hi,

I Want Conditional Formatting Formula or VBA Code For Cell D4 and E4 To Show mm/dd/yyyy By Default Whenever Become Empty !

Thanks & Regards
Raindrop

[HansV]

Right-click the sheet tab and select View Code from the context menu.
Copy the following code into the worksheet module:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("D4:E4"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Range("D4:E4"), Target)
            If cel.Value = "" Then
                cel.Value = "mm/dd/yyyy"
            End If
        Next cel
        Application.EnableEvents = True
    End If
End Sub

[Rudi]

Hans's macro with some additional cosmetics...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("D4:E4"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Range("D4:E4"), Target)
            If cel.Value = "" Then
                With cel
                    .Value = "mm/dd/yyyy"
                    .Font.Color = -5066062
                    .HorizontalAlignment = xlCenter
                End With
            Else
                cel.ClearFormats
            End If
        Next cel
        Application.EnableEvents = True
    End If
End Sub

[HansV]

I assume that cell D4 and E4 are intended for date values, so using ClearFormats may not be a good idea...

[Rudi]

Good call.
This works better...

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("D4:E4"), Target) Is Nothing Then
        Application.EnableEvents = False
        For Each cel In Intersect(Range("D4:E4"), Target)
            If cel.Value = "" Then
                With cel
                    .Value = "mm/dd/yyyy"
                    .Font.Color = -5066062
                    .HorizontalAlignment = xlCenter
                End With
            Else
                cel.Font.ColorIndex = xlAutomatic
                cel.HorizontalAlignment = xlGeneral
            End If
        Next cel
        Application.EnableEvents = True
    End If
End Sub

[raindrop]

Great ! Thank You Very Much Both Of You, I Love This Lounge.

Raindrop