Conditional Format a cell if modified

Richard0298 · #1 08-31-2023, 09:32 AM

I would like the cells in a column to highlight when changed, but only for the day. This would start over the next day. I can't use "today" because there is no date associated with the cells. I am updating pricing and don't want to highlight manually each cell as I change them, using this as a check list basically.

rollis13 · #2 09-02-2023, 09:52 AM

Have a try my macros. You can possibly modify them according to your needs.
Macro to be pasted in the sheet's module, it will highlight cells in column C if changed:

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)) Is Nothing Then Target.Interior.ColorIndex = 6
End Sub

Macro to paste into the ThisWorkBook module, when the file is opened for the first time in the day it will remove highlighting in column C cells if today's date is later than the last day the file was updated (saved):

Code:

Option Explicit
Private Sub Workbook_Open()
    If WorksheetFunction.EDate(CreateObject("Scripting.FileSystemObject").GetFile(ActiveWorkbook.Name).DateLastModified, 0) < WorksheetFunction.EDate(Now, 0) Then Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Interior.ColorIndex = xlNone
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional format a table cell based on percentage range	Kiwi971	Mail Merge	7	10-03-2022 11:25 PM
How to conditional format a table cell in word based on it's value.	JulianS96	Word VBA	9	10-20-2021 02:25 AM
Question re: Conditional formatting (cell occurs after another cell)	Saywarder	Excel	1	04-20-2015 11:29 AM
Conditional Format cell appearance Excel 2007	Gary Drumm	Excel	3	02-22-2014 01:43 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM