Cell changing color if updated within the las [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Can I set a cell to change background color if I have updated that cell during the last 10 days? Meaning that, if I update a cell it will remain colored until 10 days have passes.


thank you

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Yes you can do it but for that you have to use

Worksheet_change event of the sheet where you want this to occur. There you have to create the conditional format so that dates are hard coded. If you want to see how to do it, use macro recorder to create conditional formatting, that will give you the code
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
The excel is fairly simple, each week I need to update some cells in column D and I want these cells to color to blue as soon as I update them and remain like that for the following 7 or 10 days.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
I understand that point. Asking for book serves four purposes. It allows me to see how the book is designed. It makes it much easier to explain code. It saves me times in creating data and book. Last but not the least, it allows me to see if a person is even interested in looking for a solution, you would be amazed to see how many times there is no response once you ask for the book. I may sound rude here, but trust me it is not my intention to be rude as I try to help on my free accord and dont get any thing back in return. Thanks .
[URL=http://wikisend.com/download/593182/test.xlsx]test.xlsx[/URL]

I understand. Please find above the link.
in the test file, the column I need to color itself when updated is column L.
thank you
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Try this

1. Press ALT + F11 to start VBE
2. Press CTRL + R to launch Project Viewer
3. Double click on you sheet where you want to highlight. In your sample that would be sheet1
4. Paste the code below

Private Sub Worksheet_Change(ByVal Target As Range)  
Dim Cell As Range  

    For Each Cell In Target  
          
        With Cell  
              
            If ((.Row > 3) And (.Column = 12)) Then  
                .FormatConditions.Delete  
                .FormatConditions.Add Type:=xlExpression, _  
                    Formula1:="=""" & (Date + 10) & """  >= Today()"  
                .FormatConditions(1).Interior.ColorIndex = 41  
            End If  
              
        End With  
          
    Next Cell  
      
    Set Cell = Nothing  
      
End Sub




Note:

Today is June 11, 2010. So till June 21, 2010 (including), the cell would be blue
IT WORKS!!!!!

Thank you!!!!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
That's great. Congrats!!

Just make sure you test it by changing the system date of your PC to ensure that it is working as I think it is working. I would change it to date before today and +9, +10, +11 day from today, to make sure it is working. I am sorry but this PC is dead slow and takes for ever to start launch any application. If it does not now work, let me know.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!