Highlighting cells/rows when approaching start date

fnaclew · #1 11-15-2006, 06:47 AM

Hi there All,

I apologise if this seems quite basic or has been answered previously however I have searched for a while on t'internet and cannot find a specific response to my query.

Basically I have a basic spreadsheet showing vehicle details and if possible I want to have cells/rows change colour when the expiry date of the mot and tax is approaching.

Can anyone help or am I looking for the impossible?

Any help or advice will be greatly appreciated.

Best regards,

fnaclew

Alfaholic · #2 11-15-2006, 07:44 AM

Excel 2003 has a function called Conditional Formatting which can do this for you rather easily. It's in the Formats menu. If you struggle with using it you will find Excel's Help entries on the function are comprehensive.

Good luck!

fnaclew · #3 11-15-2006, 08:05 AM

Hi alfaholic,

Thanks for the response.

Yeah, I understand conditional formatting however I can't see how it can help me as I want the row to change to a specific colour when there is say 7 days to go before the mot expires.
Don't want much, do I? lol

I've seen similar on data sheets where I used to work so I'm sure there is a way but personally can't find out how.

Thanks again.

Jon Quixley · #4 11-15-2006, 08:57 AM

See the zipped xls file and check out the layers of conditional formatting to see how it will cahnge as you go from more than 21 days to between 7 and 14 days and finally less than 7 days.

Cheers
JQ

**oldchippy** · #5 11-15-2006, 08:57 AM

Do you mean something like the attached

fnaclew · #6 11-15-2006, 09:42 AM

jq,
I can see what you mean but I only have one known date within the data e.g. Mot expiry date.

oldchippy,
not sure I get what you're suggesting.

Mind you, I feel as though I'm learning something, which is fantastic! Thanks alot both.

I think I need something that might combine both suggestions. It needs to use todays date to read how long till expiry then once within a range (use 'between' in con. frm.?) e.g. 14 days of expiry the row to change from white say to orange, then 7 days to red.

I would include an example but I'm unable to attach a zip file as we don't have a zip application!

Hope some of this make sense!

Again, thanks very much.

fnaclew · #7 11-15-2006, 09:50 AM

A ha!!

An example?

**oldchippy** · #8 11-15-2006, 09:51 AM

Format your date cell to this formatting, it doesn't matter how many you have got. When it get to your MOT date hurry and get the car sorted out!

fnaclew · #9 11-15-2006, 10:11 AM

Quote:

Originally Posted by oldchippy

When it get to your MOT date hurry and get the car sorted out!

PMSL!!!

Will try immediately. Ta.

fnaclew · #10 11-15-2006, 10:19 AM

oldchippy,

I replied before opening your file.
Your list of dates, what reference point are they taking from?
You have a column of dates but...?

Sorry to be a pain.

If you feel I'm missing the bleedin' obvious and can't be bothered to exlain futher I'll understand!!! lol

fnaclew · #11 11-15-2006, 10:23 AM

Quote:

Originally Posted by oldchippy

Format your date cell to this formatting, it doesn't matter how many you have got.

...date cell...??

**oldchippy** · #12 11-15-2006, 10:24 AM

We all started where you are, so let me explain. Ignore my column of date I only did that to show you the formatting that can be applied to cells that dates are within 14 days of TODAY().

What you need to do is apply this formatting to you date cells in your spreadsheet. Once you have done one, you can double-click on the Paintbrush icon and click on all your date cells to apply the same formatting.

Try it on a test spreadsheet, so you understand what's going on.

OK?

**oldchippy** · #13 11-15-2006, 10:27 AM

Quote:

Originally Posted by fnaclew

...date cell...??

I'm assumming that the MOT and servicing dates are in a cell/s on the spreadsheet and not still on the MOT certificate. I know Excel is good, but not that good!

fnaclew · #14 11-15-2006, 10:30 AM

Quote:

Originally Posted by oldchippy

I'm assumming that the MOT and servicing dates are in a cell/s on the spreadsheet and not still on the MOT certificate. I know Excel is good, but not that good!

You funny!

I like!!

fnaclew · #15 11-15-2006, 11:42 AM

oldchippy,

Thanks for your patience.

I have done as you recommended however nothing has happened.
I thought maybe it might be reading away from the mot date rather than up to so I reversed the between points. Still no good.

Am I missing something obvious, a case of can't see for looking I wonder?

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)