Hi all,
I'm having an issue with this one.
I have uploaded an example workbook.
I need a code which fires some other codewhenever a range of cells change which are within 5 days of todays date. Hence, the msgbox would fire if anything in yellow was changed (B16:D21) and then tomorrow the range would be slightly different (B17:D22), etc
MsgBox ("Test")
Todays date will always be in A1.
For your sheet code:
note in attachment there is now out-or range date in A1Private Sub Worksheet_Change(ByVal Target As Range) Dim todays_date As Range If Not Intersect(Target, Columns("B:C")) Is Nothing Then Set todays_date = Range("A2:A100").Find(what:=Range("A1"), lookat:=xlWhole) If Not todays_date Is Nothing Then If Not Intersect(Target, todays_date.Offset(0, 1).Resize(6, 3)) Is Nothing Then MsgBox "Spotted change in: " & todays_date.Offset(0, 1).Resize(6, 3).Address End If End If End If End Sub
Best Regards,
Kaper
Hi Kaper,
Thanks. I downloaded your file and changed all the cells in yellow. No MsgBox appeared?
let me repeat (may be it was in small print :-)
note in attachment there is now out-or range date in A1 - i left it after tests what happens if there is no match so change A1 again to TODAY, enable macros, and it shall work.
Great! Thanks Kaper.
Sorry for the late reply. My bad.
Hi Kaper,
Sorry - not sure if I should start a new thread or not? - but how do I adjust your code so that it identifies the actual cell that has been changed in the MsgBox?
It's ok I've sorted it - I just removed this if anyone else wants to know...
Offset(0, 1).Resize(6, 3).
Glad you did it. I was off-line few last days. But you did right.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks