Date in multiple formats

asdsparky

Board Regular
Joined
Oct 13, 2017
Messages
60
I have the following code in my workbook to populate the date in certain cells when the cells are selected. The formula works to populate both ranges. The issue I am running into is formatting the date differently for the two ranges. I need the first range (cell "E4") to format as "mmmm yyyy" which I have done and it works. The other range "G13:G400" needs to format as "mm/dd/yy". Any ideas? I have tried running each range as a separate string, but no luck.
Code:
With ActiveCell
            If Intersect(range("E4", "G13:G400"), .Cells) Is Nothing Then Exit Sub
           .Value = Date
           .NumberFormat = ("mmmm yyyy")
 End With
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Please post the entire procedure that this block of code is found in.
 
Upvote 0
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As range)
  Dim Green As Long, Red As Long, Gray As Long, Black As Long, Clear As Long
  Green = RGB(201, 221, 176)
'  Yellow = RGB(255, 237, 153)
  Red = RGB(253, 173, 150)
  Gray = RGB(118, 122, 121)
  Black = RGB(0, 0, 0)
  Clear = xlNone
  
 If Target.EntireRow.Font.Bold = False Then
  If Target.CountLarge = 1 Then
    If Target.Row > 12 And Target.Column > 8 And Target.Column < 13 And Target.Count = 1 Then
        With Intersect(Target.EntireRow, Columns("A:L"))
        Intersect(.Cells, Columns("I:L")).ClearContents
        Target.Value = Chr(252)
        Target.Font.Name = "Wingdings"
        Target.Font.Size = 10
        Target.HorizontalAlignment = xlCenter
         .Cells.Interior.Color = Choose(Target.Column - 8, Green, Red, Gray, Clear)
         .Cells.Font.Color = Choose(Target.Column - 8, Black, Black, Black, Black)
        End With
    End If
   End If
  End If
     
 With ActiveCell
            If Intersect(range("E4", "G13:G400"), .Cells) Is Nothing Then Exit Sub
           .Value = Date
           .NumberFormat = ("mmmm yyyy")
 End With
    
End Sub
 
Upvote 0
*Update: The way it is currently written, it also causes every cell in columns E, F & G to insert the date when selected. This is no bueno.
 
Upvote 0
OK. I have no idea if your "upper code" is interfering, but this should replace the first block of code you posted, and do what you want:
Code:
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Intersect(Target, Range("E4"))
    If Not rng1 Is Nothing Then
        With Target
           .Value = Date
           .NumberFormat = "mmmm yyyy"
        End With
    End If
    
    Set rng2 = Intersect(Target, Range("G13:G400"))
    If Not rng2 Is Nothing Then
        With Target
           .Value = Date
           .NumberFormat = "mm/dd/yyyy"
        End With
    End If
Note: Typically, you do not use "ActiveCell" in Event Procedure code. You usually reference "Target".
 
Last edited:
Upvote 0
*Update: The way it is currently written, it also causes every cell in columns E, F & G to insert the date when selected. This is no bueno.
I fixed this part of the problem by changing my range to ("E4, G13:G400"), basically removing one set of "".
 
Upvote 0
OK. I have no idea if your "upper code" is interfering, but this should replace the first block of code you posted, and do what you want:
Code:
    Dim rng1 As Range
    Dim rng2 As Range
    
    Set rng1 = Intersect(Target, Range("E4"))
    If Not rng1 Is Nothing Then
        With Target
           .Value = Date
           .NumberFormat = "mmmm yyyy"
        End With
    End If
    
    Set rng2 = Intersect(Target, Range("G13:G400"))
    If Not rng2 Is Nothing Then
        With Target
           .Value = Date
           .NumberFormat = "mm/dd/yyyy"
        End With
    End If
Note: Typically, you do not use "ActiveCell" in Event Procedure code. You usually reference "Target".

The "ActiveCell" code was actually one of the first written for this spreadsheet. The rest was added as the spreadsheet grew and developed. It just happened to work when I only needed Cell E4 to change.
Thank you this re-write. It works flawlessly. :pray:
 
Upvote 0
Joe, I spoke too soon...I found a flaw. everything works as desired when I select a cell that needs the date in the format desired however, if I select an entire row or column that contains one of the target ranges, the contents of every cell in the row gets replaced with the formatted date. This causes me to lose all of the data in those cells. I have to close without saving in order to maintain the data, which is not good if I have already spent half the day entering data and suddenly need to add a row. Am I making sense?
 
Upvote 0
We can limit it to exactly just those cells in the "intersection" instead of the "Target" (selected) cells like this:
Code:
    Dim rng1 As Range
    Dim rng2 As Range
    Dim cell As Range
    
    Set rng1 = Intersect(Target, Range("E4"))
    If Not rng1 Is Nothing Then
        For Each cell In rng1
            With cell
                .Value = Date
                .NumberFormat = "mmmm yyyy"
            End With
        Next cell
    End If
    
    Set rng2 = Intersect(Target, Range("G13:G400"))
    If Not rng2 Is Nothing Then
        For Each cell In rng1
            With cell
                .Value = Date
                .NumberFormat = "mm/dd/yyyy"
            End With
        Next cell
    End If
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top