Date in multiple formats

[asdsparky]

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.

With ActiveCell
            If Intersect(range("E4", "G13:G400"), .Cells) Is Nothing Then Exit Sub
           .Value = Date
           .NumberFormat = ("mmmm yyyy")
 End With

 

[Joe4]

Please post the entire procedure that this block of code is found in.

 

[asdsparky]

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

 

[asdsparky]

*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.

 

[Joe4]

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:

    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".

 

[asdsparky]

*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 "".

 

[asdsparky]

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:

    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. ray:

 

[Joe4]

Thank you this re-write. It works flawlessly.

You are welcome!

 

[asdsparky]

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?

 

[Joe4]

We can limit it to exactly just those cells in the "intersection" instead of the "Target" (selected) cells like this:

    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