conditional formatting for new data added to a spreadsheet within the last week

[jingles9]

I have an excel 2007 spreadsheet with 9 columns. I want to add a formula that will change the colour of the entire row (within a set range) if something new has been added to the spreadsheet within the last week. Do you know what formula to use to do this?
I also want to be able to change the colour of the entire row (within the same selected range) if the deadline is within 14 days.

[Pete_UK]

Do you have a column which records when the data was added? Do you have another column to record the deadline date?

Pete

[jingles9]

There is a column with the deadline date but there is no column stating the start date just the last date worked which is not necessarily a recent date

[Pete_UK]

So how do you expect Excel to know that data has been added within the last week?

Pete

[jingles9]

Hi Pete, yes...I figured if it can filter data that has been added within a timeframe, there has to be a way to use a formula or conditional formatting to highlight the changed/added cells. There are a lot of article referring to this, but they do not give the steps on how to do it.

[Spitzerpl]

...There are a lot of article referring to this, but they do not give the steps on how to do it.

I think the articles you are referring to us VBA to accomplish this. It cannot be done with a formula as far as I know. You can find the reference to the last cell updated with a formula but not the cells that have been modified within the last week. Hope someone proves me wrong, though!

[jingles9]

Hi Everyone
Thanks for your help with this. If I were to add another column to allow the user to input the date the data was added to the spreadsheet (say column A) would you know what formula to use to highlight the entire row if that data was added within the last week? I also want to know a formula to highlight the row a different colour if the data is set to expire within 14 days. The Date used for that formula would be Column F.
There are a couple of pre-set conditional formatting examples I could use, but it only highlights the cell and not the entire row.

[Spitzerpl]

Please Login or Register  to view this content.

I think. I'm not at a computer to validate it.

[Pete_UK]

Highlight all the data that this is to apply to, and extend to a few more rows to allow for new records, e.g. from A2 to J200 (I've assumed that you have a header row). Then bring up the Conditional Formatting dialogue box, choose New Rule and then select Use a Formula ... from the bottom of the list. Put this formula in:

=AND($A2>=TODAY()-7,$A2<=TODAY())

Then click on the Format button | Fill tab and choose your colour. OK your way out.

Repeat for the second condition, but this time use the formula:

=AND($F2>=TODAY(),$F2<=TODAY()+14)

and again click the Format button and choose your colour for the expiry condition.

Once you exit the dialogue box (or click Apply), Excel will automatically adjust the cell references on each row to suit your data, but if you add more data in the future you can just use the Format Painter icon to apply those CFs to the new rows of data if Excel doesn't apply them automatically. Note that there is a hierarchy with conditional formatting, so if the first condition is met then that will be applied, so if expiry date is more important to you that data entry within the last week, then you can move that condition up to take priority.

Hope this helps.

Pete

[jingles9]

Thanks again to everyone. Is there a way to tweak the formula to also show if the expiry has passed as well?

[Pete_UK]

Is this a third condition/colour or do you want it to be included within one or both of the other conditions? For example, you might put some data in today, but the expiry date has passed (i.e. late data entry).

It is better to ask all your questions up front rather than drip-feed them over time - some contributors get fed up with lots of new questions and can abandon the thread.

Pete

[jingles9]

Hi Pete
It would be included with this formula =AND($F2>=TODAY(),$F2<=TODAY()+14)
Thanks for the feedback...I will try to ask all questions up front. I am new to this and I didn't realize that I would need additional formulas. I figured it would automatically keep it highlighted if it had passed. I don't want to irritate anyone....sorry

[Pete_UK]

OK, well that formula is only triggering the colour if the expiry date is within the next 14 days (i.e. from today up to today + 14), which is what you asked for originally. Use Edit on the CF dialogue box to amend the formula to this to accommodate your latest request:

=AND($F2<>"",$F2<=TODAY()+14)

As you are new you are advised to read through the Forum Rules (at the top of the screen). Note, in particular, that you can mark a thread as Solved if you consider it to be so - click on Thread Tools above your first post in this thread. Also, you can thank contributors directly by clicking on the "star" icon in the bottom left corner of any post you have found to be helpful (not just on this thread, and even on threads that you are not subscribed to). You can also attach workbooks and other files to your posts - the FAQ describes how to.

Hope this helps.

Pete