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

 

[asdsparky]

That did eliminate filling every cell in the selected row with the date. It still enters the date in the "F#" cell when the row is selected, rather than only when that particular cell is selected. I tried modifying slightly to this:

    Dim rng1 As Range
    Dim rng2 As Range
    Dim cell As Range
    
    Set rng1 = Intersect(Target, Range("F2"))
    If Not rng1 Is Nothing Then
        For Each cell In rng1
            With Activecell
                .Value = Date
                .NumberFormat = "mmmm yyyy"
            End With
        Next cell
    End If
    
    Set rng2 = Intersect(Target, Range("F9:F400"))
    If Not rng2 Is Nothing Then
        For Each cell In rng2
            With Activecell
                .Value = Date
                .NumberFormat = "mm/dd/yyyy"
            End With
        Next cell
    End If

It prevents the "F" cell from getting the date when I select the row or column but now it changes the value of the first cell in the selected row or column to the date.

 

[Joe4]

Then let's update the code to bail out if more than one cell is selected (i.e. selecting a whole row or column).
Because of that, we should no longer need to loop through the intersection (since it can only be one cell now).

    Dim rng1 As Range
    Dim rng2 As Range
    
[COLOR=#ff0000]    If Target.Count > 1 Then Exit Sub[/COLOR]

    Set rng1 = Intersect(Target, Range("F2"))
    If Not rng1 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mmmm yyyy"
        End With
    End If
    
    Set rng2 = Intersect(Target, Range("F9:F400"))
    If Not rng2 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
        End With
    End If

Note: Try to get in the habit of using "Target" instead of "ActiveCell" in Worksheet_SelectionChange and Worksheet_Change event procedures!
"Target" is the cell that triggered the code to run, and doesn't change within the procedure.
"ActiveCell" could actually change within the procedure, depending on what your code does.
Well-written code will seldom ever use "ActiveCell".

 

[asdsparky]

Good call. I thought I had another issue because this update took me back to square one, where the date would only work in the "F9:F400" range. I played with it until I found the culprit, which leads me to think that maybe my very first code would have worked had I noticed this in the first place (I will be checking that one). By limiting the target count to 1 I inadvertently canceled out the functionality for the "F2" range because "F2" is merged with "E2", causing the target count to exceed 1. I changed the code as follows and it seems to be working now:

    Dim rng1 As range
    Dim rng2 As range
    
    If Target.Count < 3 Then
    Set rng1 = Intersect(Target, range("E2"))
    If Not rng1 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mmmm yyyy"
        End With
    End If
    
    Set rng2 = Intersect(Target, range("F9:F400"))
    If Not rng2 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mm/dd"
        End With
    End If
    End If

Thanks so much for your help with this.

 

[asdsparky]

I further modified it because now if I accidentally grab 2 cells, the date goes into both in the "F9:F400" range. I corrected this as shown below. I think we now have a final solution

    Dim rng1 As range
    Dim rng2 As range
    
    If Target.Count < 3 Then
    Set rng1 = Intersect(Target, range("E2"))
    If Not rng1 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mmmm yyyy"
        End With
    End If
    End If
    
    If Target.Count = 1 Then
    Set rng2 = Intersect(Target, range("F9:F400"))
    If Not rng2 Is Nothing Then
        With Target
            .Value = Date
            .NumberFormat = "mm/dd"
        End With
    End If
    End If