Excel and conditional formatting

  exdragon 13:46 03 May 2006

Hi - I'm keeping a list of invoices prepared, with the dates they are sent out. Can I format the adjacent cells in the next column so that when the due date is reached (date sent plus 30 days), the cell changes colour?

There aren't very many invoices and although I can do something useful in our accounts package, a single spreadsheet with visible reminders would be very handy!

  VoG II 13:54 03 May 2006

Select Formula Is and use


Adjust the reference to A1 to the correct column and row. You can then use the Format Painter to apply the format to a range of cells.

  exdragon 13:57 03 May 2006

Sorry VoG™ - where's Formula Is?

  VoG II 14:01 03 May 2006

Click in the cell you want to format. Format > Conditional Formatting. The dialog that should pop-up has a drop down list that is Cell Value Is as the default. Click the arrow to select Formula Is and type the formula in the box. Then click the Format... button, select the formatting to apply and OK your way out.

  exdragon 14:03 03 May 2006

Of course it is! Thank you - I was getting mixed up with the formula bar. Off to work now to impress myself!!

  exdragon 18:15 03 May 2006

Why don't I read what you tell me?? I've spent ages trying to work out what's wrong and just realised I'm using Cell value instead of Formula is..

Oh well.

  exdragon 08:16 06 May 2006

VoG™'s advice worked perfectly - now, how can I take it one step further? I'd like to be notified when invoices become payable.

I've got a columns for the date I send an invoice, and the adjacent one for the date it's paid. I can apply the formula VoG™ gave me, but the set up is that I have a list of, say, 20 clients but only fill in the invoice date when the service we give them is complete. If I apply the same formula, it colours all the cells which don't already include the invoice date.

The invoice dates aren't entered in sequence, as some services take longer to complete than others.

I think what I'm after is 'colour the cell if it's more than 30 days after this particular date, otherwise leave it uncoloured'. Unless it's easier just to use the formula painter as I go?

VoG™ - where can I get info on basic formulae such as these? I don't want a huge book which tells me everything from how to put a border round cell to how to run the world from a spreadsheet! I think I get a mnetal block about nested formulae, what, if, and and but!

  VoG II 08:30 06 May 2006

To prevent the conditional formatting applying when the date cell is empty, modify the formula to


You could try click here for a start.

  exdragon 08:34 06 May 2006

Many thanks - the site looks useful, bookmarked.

Maybe I'll stop bothering you now, but that's not a guarantee!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique