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
Whenever Cell Becomes Empty Then Displays Custom Text
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Whenever Cell Becomes Empty Then Displays Custom Text
Right-click the sheet tab and select View Code from the context menu.
Copy the following code into the worksheet module:
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Whenever Cell Becomes Empty Then Displays Custom Text
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Whenever Cell Becomes Empty Then Displays Custom Text
I assume that cell D4 and E4 are intended for date values, so using ClearFormats may not be a good idea...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Whenever Cell Becomes Empty Then Displays Custom Text
Good call.
This works better...
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 36
- Joined: 04 Feb 2013, 06:22
Re: Whenever Cell Becomes Empty Then Displays Custom Text
Great ! Thank You Very Much Both Of You, I Love This Lounge.
Raindrop
Raindrop