Posts by alphaburger

alphaburger · Jul 11th 2019

I fixed my code. Ended up using this, can wrap up this in a loop..

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


Dim xOffsetColumn As Integer


xOffsetColumn = 55


    If Not Intersect(Target, Me.Range("G4:G20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn) = Date
    End If


    If Not Intersect(Target, Me.Range("I4:I20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 1) = Date
    End If
    
    If Not Intersect(Target, Me.Range("K4:K20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 2) = Date
    End If


    If Not Intersect(Target, Me.Range("M4:M20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 3) = Date
    End If


    If Not Intersect(Target, Me.Range("O4:O20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 4) = Date
    End If
    
    If Not Intersect(Target, Me.Range("Q4:Q20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 5) = Date
    End If


    If Not Intersect(Target, Me.Range("S4:S20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 6) = Date
    End If


    If Not Intersect(Target, Me.Range("U4:U20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 7) = Date
    End If
    
    If Not Intersect(Target, Me.Range("V4:V20000")) Is Nothing Then
        Target.Offset(, xOffsetColumn - 8) = Date
    End If


End Sub

Display More

This actually inputs the date in the right columns. Any insight to why I had to offset as minus and not plus?

alphaburger · Jul 11th 2019

Hi, so I have a fairly basic problem that's the last link to a PM tool i've built.

I have several cells in the same row with drop down lists. When an item is selected from the list I want to record the date in another cell in the same row.

I've managed to get it working for the first cell, but the code doesn't any other cells.

Here is my code:

Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


Dim xOffsetColumn1 As Integer
Dim xOffsetColumn2 As Integer
Dim xOffsetColumn3 As Integer
Dim xOffsetColumn4 As Integer
Dim xOffsetColumn5 As Integer
Dim xOffsetColumn6 As Integer
Dim xOffsetColumn7 As Integer
Dim xOffsetColumn8 As Integer
Dim xOffsetColumn9 As Integer




xOffsetColumn1 = 55
xOffsetColumn2 = 56
xOffsetColumn3 = 57
xOffsetColumn4 = 58
xOffsetColumn5 = 59
xOffsetColumn6 = 60
xOffsetColumn7 = 61
xOffsetColumn8 = 62
xOffsetColumn9 = 63


If Not Intersect(Target, Me.Range("G:G")) Is Nothing Then


    Target.Offset(, xOffsetColumn1).Value = Now
    Target.Offset(, xOffsetColumn1).NumberFormat = "dd-mm-yyyy"


End If




If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then


    Target.Offset(, xOffsetColumn2).Value = Now
    Target.Offset(, xOffsetColumn2).NumberFormat = "dd-mm-yyyy"


End If
End Sub

Display More

I will need to do this for 9 different ranges, Column G,I,K,M,O,Q,S,U,V.

I wont be sleeping till I get this working for tomorrow, so any help would be SUPERR DUPER APPRECIATED

Thanks in advance.

Posts by alphaburger

Worksheet Change to update multiple cells

Worksheet Change to update multiple cells