How can I highlight cells that have been changes since "X" date ?

[momentumons]

Hello! I have a mega forecast that people update and I'd like to make it simple to see which cells have been updated (could be added as a new line or info deleted or changed in a cell).

The model already has lots of conditional formatting indicating various things so it needs to work on-top of this.

I was thinking there would be a way to "put a red box" around cells (in a specific table range) that have been added or changed since "X" date (as specified by a cell in the same worksheet but on a different tab).

For example: I might have a cell that had $10,000 last week, but it was changed to $5,000 today. How can I make that specific cell have a red box around it (as long as today's date is > the date specific as above).

Does that make sense?

Thanks for you help!

 

[Yongle]

You could modify this to suit your requirements

My result after modifying E3 & E7

Test in a NEW workbook
- carry out the instructions in specified sequence to prevent code failing due to missing objects etc

1. Insert a new sheet (Sheet2) and rename it "Log"

2. In Sheet1, select cells E2:E10
- click on Conditional Formatting
- add a new rule using this formula
=COUNTIF(Log!A:A,ADDRESS(ROW(E2),COLUMN(E2)))>0
- select the desired red box outline format

3. Insert code below in Sheet1's code window
- it will not work in a module like Module1
(right click on sheet1 tab \ View Code \ paste into that window)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    If Not Intersect(Range("E2:E10"), Target) Is Nothing Then
        For Each cel In Target
            Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(cel.Address, Date, cel.Value)
        Next cel
    End If
End Sub

4. Insert values 20,30,40 ...90 in E2,E3...E10 in Sheet1
... red outline should appear around each one ... and the log should look like this:

5. Next manually amend the first 4 dates in the log to 1 April

6. In cell A1 in sheet1 enter date 2 April

7. insert the macro below in a new Module and run it

Sub ClearLog()
    Dim r As Long, log As Worksheet
    Set log = Sheets("Log")
    For r = log.Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        If log.Cells(r, 2) < Sheets("Sheet1").Range("A1") Then log.Rows(r).EntireRow.Delete
    Next r
End Sub

8 cells E2:E5 conditional formatting should have changed to no outline

 

[momentumons]

Thank you @Yongle ! That looks good... but the range of data I need to include for the 'red squares' is 13 columns in an ever expanding table (It;s 12 months + a total and the rows of data and constantly updated. It's basically L:X or the table is called FORECAST and the Columns are Jul-19:TOTAL

The above works when there are only 10 cells to include in the log but I have an almost infinite amount... Can you help ?

 

[Yongle]

Try this amendment

If Not Intersect(Range("L:X"), Target) Is Nothing Then

 

[momentumons]

@Yongle ! I've been trying to work through this but I am getting stuck. I have tried to follow your instructions above and I can get the red squares around the numbers but they don't disappear...? Also, I cannot work out how to edit this for my purpose... In my spreadsheet I don't have a list of dates to read off like in "sheet 1" above. I was hoping i could add the conditional formatting to the table any time cells in it were modified...? Sorry I am not understanding your solution. Can you help?

 

[Yongle]

I've been trying to work through this but I am getting stuck. I have tried to follow your instructions above and I can get the red squares around the numbers but they don't disappear...?

when should they disappear ?

 

[Yongle]

In my spreadsheet I don't have a list of dates to read off like in "sheet 1" above.

Sheet1 does not contain any dates EXCEPT the date to use when clearing dates from the log