Date stamp on multiple columns is the same sheet

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
163
Office Version
  1. 2016
  2. 2011
  3. 2007
Platform
  1. Windows
I thank @Peter_SSs for helping me out in Nov 19 with the code below:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ws2 As Worksheet
  Dim myRange As Range, Changed As Range, cel As Range
  
  Set myRange = Range("E5:BX24")
  Set ws2 = Sheets("Sheet2")
  Set Changed = Intersect(myRange, Target)
  If Not Changed Is Nothing Then
    ws2.Unprotect Password:="yourpassword"
    For Each cel In Changed
      ws2.Range(cel.Address).Value = IIf(Len(cel.Value) > 0, Date, "")
    Next cel
    ws2.Protect Password:="yourpassword"
  End If
End Sub

Can anyone help me out with slight modification so that I can get the dates stamped for multiple ranges in the same sheet?!

Here is what I am looking for:

1. My data input range is C7:C106, G7:G106, K7:K106, O7:O106 and S7:S106 and I need 'Date' and 'Day' [format# 02 Feb 20 (Sun)] stampped at B7:B106, F7:F106, J7:J106, N7:N106 and R7:R106 respectively. All ranges (input+output) are in the same sheet.
2. I want the date stamp removed fromB7:B106, F7:F106, J7:J106, N7:N106 and R7:R106 if user deletes entries at C7:C106, G7:G106, K7:K106, O7:O106 and S7:S106.
3. The sheet is password protected. The workbook will also be protected (same password).
4. Users will use Excel 2007 to 2016.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Chng As Range, Cl As Range
   
   Set Chng = Intersect(Target, Range("C7:C106, G7:G106,K7:K106,O7:O106,S7:S106"))
   If Not Chng Is Nothing Then
      Me.Unprotect "yourpassword"
      For Each Cl In Chng
         If Cl.Value = "" Then
            Cl.Offset(, -1).Value = ""
         Else
            Cl.Offset(, -1).Value = Format(Date, "dd mmm yy (ddd)")
         End If
      Next Cl
      Me.Protect "yourpassword"
   End If
End Sub
 
Upvote 0
Solution
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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