I have a code that is triggered by user entry in column K and P: If any value is inserted in "K" then offset to corresponding cell in column R and fills the username. If any value is inserted in "P" then offset to corresponding cell in column Q and fills the data using TODAY().
My code looks like:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserName As String
UserName = Environ("username")
If Target.Column <> 11 Then Exit Sub
Target.Offset(, 7).Value = IIf(Target.Value = "", "", UserName)
If Target.Column <> 16 Then Exit Sub
Target.Offset(, 1).Value = IIf(Target.Value = "", "", Now)
End Sub
The problem is that it is only working with the first IF, how can I get it to work with both?
Thank you in advance!!
CodePudding user response:
Test each cell in the target range.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Target.Cells
If cell.Column = 11 Then
cell.Offset(, 7).Value = IIf(cell.Value = "", "", Environ("username"))
ElseIf cell.Column = 16 Then
cell.Offset(, 1).Value = IIf(cell.Value = "", "", Now)
End If
Next
Application.EnableEvents = True
End Sub