Coloured Cells and Dates

  • Thread starter Thread starter kiza
  • Start date Start date
K

kiza

I have a spreadsheet which consists of dates and coloured background
within the same cell.

If the dates have passed, then they have coloured backgrounds however
if the dates are in the future, then they are not coloured.

I am trying to think of a way in which I can use a macro to move alon
the cells looking for dates which have passed. If they have passed an
do not have coloured backgrounds, I need the macro to add a coloure
background.

Is there a way in which I can use a macro to colour in a background i
the date has passed but not colour it in if the background has a colou
and the date has passed?

Any help would be appreciated. Many Thanks.

Kiz
 
Kiza,

You can use conditional formatting without macros.

Select all of the cells (let's assume A1 is the first)
Goto menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1<TODAY()
Click Format
Select thye patterns tab
Choose a colour
OK
OK

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Kiza

why not use conditional formatting ?

Select all the cells with dates and then enter the following:

Format | Conditional formatting...

Set condition 1 to Formula is: =A1<TODAY() and set the background format to
red
(format | patterns | pick red)
click the add >> button
Set condition 2 to Formula is: =A1=TODAY() and set the background format to
amber
(as above with amber)
click the add >> button
Set condition 3 to Formula is: =A1>TODAY() and set the background format to
green
(as above with green)

Pick colours of your choice ...

Regards

Trevor
 
Back
Top