masud8956
Board Regular
- Joined
- Oct 22, 2016
- Messages
- 163
- Office Version
- 2016
- 2011
- 2007
- Platform
- Windows
I thank @Peter_SSs for helping me out in Nov 19 with the code below:
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.
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.