Changing the Value of a Cell When a User Clicks a Specific Cell - Need Help With Efficiency

[Polar]

Hi there! I have a program that I'm trying to write that displays a date in a cell based on whether a user clicks on a specific cell.

Here are a few notes:
The date is formatted as YYYMMDD
A4 - Contains the first half of the date (ex YYYYMM)
M4 - Displays the final date in YYMMMDD format
F/G5 - Cell that the user will click to trigger change in cell M4
F/G4 - Cell that contains the number that will complete the day portion of the "date"

This is just a smaller test for just two cells, however this will be used on 18 cells...so I imagine this code will start getting really big. Rather than copying the same code over 18x, does anyone know of any ways to make this shorter, and more efficient?

Here's what I put together so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  
With Target
    If .Address = Range("F5").Address Then
         If IsEmpty(Range("F5").Value) = True Then
            'do nothing
        Else
             If Range("F4").Value < 10 Then
                Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & "0" & Sheet1.Range("F4").Value
             Else
                Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & Sheet1.Range("F4").Value
            End If 
         End If 
    Else
         If .Address = Range("G5").Address Then
             If IsEmpty(Range("G5").Value) = True Then
                'do nothing
             Else
                 If Range("G4").Value < 10 Then
                    Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & "0" & Sheet1.Range("G4").Value
                 Else
                    Sheet1.Range("M4").Value = Sheet1.Range("A4").Value & Sheet1.Range("G4").Value
                End If 
             End If 
         Else
            'repeat code above for other range
         End If 
     End If 
End With
Application.EnableEvents = True
End Sub

Any help/tips appreciated!

 

[DanteAmor]

Well I'm still lost, I hope Eric understands.


If you explain the following, it may be clearer to me.


If I select cell F5, what should happen?
If I select G5, what should happen?
If I select H5, what should happen?
If I select I5, what should happen?

 

[Eric W]

Well, you should be able to adapt the first version of post 8 easy enough, just put your correct values in the Range("...") part of the red line. Try it and let us know if you have trouble. The 2-line version should be:

    If Intersect(Target, Range("F5:J15")) Is Nothing Then Exit Sub
    If Target.Row Mod 2 = 0 Then Exit Sub

which has a shorter Intersect line, then adds another line to skip even rows.

 

[Polar]

THIS IS IT! Thank you so much Eric ♫꒰･‿･๑꒱
It works like a charm. Thank you so much for all your help with this.

Quick question:
I ran into a compile error (argument not optional) with this line:

 If Range.Row Mod 2 = 0 Then Exit Sub

But then I noticed you changed it to:

 If Target.Row Mod 2 = 0 Then Exit Sub

I guess my last question would be...can you change the former to:

 If Range("insert range here").Row Mod 2 = 0 Then Exit Sub

and still get the same result? I guess the key is that you wont have to keep updating range "address" if you use Target.Row, right?

 

[Eric W]

You are correct on both counts. If you use Range, you need to give it an actual range, like Range("B17"), and that will work as you surmise. But you're also correct in that if you use Target, you don't have to worry about putting in a hard-coded range. Target will always points to the selected cell.

Glad we could help!