Cell changing color if updated within the las

Solved/Closed
haros - Jun 9, 2010 at 06:42 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Jun 11, 2010 at 08:16 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 9, 2010 at 07:13 AM
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
0
Thank you,

But can you be a bit more detailed as I quite new at excel?! :(
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 10, 2010 at 11:05 AM
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
0
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.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 10, 2010 at 04:30 PM
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 .
0
[URL=http://wikisend.com/download/593182/test.xlsx]test.xlsx[/URL]

I understand. Please find above the link.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 11, 2010 at 07:55 AM
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
0
IT WORKS!!!!!

Thank you!!!!!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 11, 2010 at 08:16 AM
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.
0