Forum Discussion
Solved
Change the value in one cell when a date changes occurs in another cell
Hi,
I've seen a few similar posts but can't quite find what I'm looking for, hopefully someone can help.
I'm looking for a macro that will change a value from 'Y' to 'N' when a date change occurs in the previous column, for example as below. If a date change occurs in column C, the value in column D changes to 'N' (column C pulls the date from another location using an XLOOKUP, and I want to be alerted if there is a change date).
Any help would be much appreciated, thanks in advance
Got what I needed as below, can't take credit as found this elsewhere and tweaked to my own needs,but seems to do the job...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rw As Long
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 4 - ActiveCell.Column)
.Value = "N"
End With
Else
rCell.Offset(0, 4 - ActiveCell.Column).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
1 Reply
Got what I needed as below, can't take credit as found this elsewhere and tweaked to my own needs,but seems to do the job...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rw As Long
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("B:C"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 4 - ActiveCell.Column)
.Value = "N"
End With
Else
rCell.Offset(0, 4 - ActiveCell.Column).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub