Highlight Cell When Value Changes, Reset On Re-Open

Evernight2021

New Member
Joined
Dec 28, 2018
Messages
10
I have a column of dates (N) in date format (MM/DD/YYY), another column that counts total years worked (M), and a column with starting date (D) also in (MM/DD/YYY) format.

I'm trying to get it so that when today's date is equal to or beyond the date in column N, the years worked (M) gets incremented and then highlighted in red to make the change easy to pick out. I'm also wondering if it's possible to make it so that the next time the workbook is opened, the red cells are either changed to green or back to white and then any new column M changes would then be highlighted in red to make the new changes easy to pick out. The user would save the workbook to keep the changes to the dates after use.[TABLE="width: 990"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I was able to calculate the years by applying the following formula to the cells in the M colum:

Code:
[FONT=Verdana]=DATEDIF(D12,TODAY(),"y")[/FONT]

I set the sheet to change colors of cells with value changes using:

Code:
[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 3
End Sub[/FONT]

Is there a way to apply that just to the 'M' column?

I created a button to remove the colors for next use with:
Code:
[FONT=Verdana]Private Sub CommandButton3_Click()
    With ActiveSheet
        Cells.Interior.ColorIndex = xlNone
    End With
End Sub[/FONT]

Individually, all of these work fine. However, if I add a new start date of 1/1/2018 and anniversary date of 1/1/2019 for example, set my computer date to 1/1/2019, and open the workbook, the cell in column M (years) will increment properly, however the cell doesn't change color. It does change color if I manually make the changes. Is there something I'm missing in order to get the color change to work?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top