HELP - Automatic Conditional formatting

Hello,

I have a spreadsheet that basically holds date values in one column going
down.

Basically what i want to do is say "If the date in a cell is 1 day away then
turn the cell fill color red, if it's 2-3 days away turn it yellow"

Is there a one liner that can be used as a global statement within the sheet
to evaluate all cells on the sheet and change color for those that satisfy
the condition or must this be done cell by cell?

Please help

You could do it with conditional formatting.

Select all the affected cells, then do Data=>Formatting => conditional
formatting.

Change cell Value is to Formula is

(assume A1 is the active cell in the selection - you set up the formula
relative to the active cell and use relative and absolute references as
appropropriate)

=(A1-today())<=1.5

Select the format button and select the red pattern

add a second condition

=(A1-today())<3.5

select the format button and select the yellow pattern

--
Regards,
Tom Ogilvy


"Adam" <Adam@discussions.microsoft.com> wrote in message
news:C1BE7FE4-0043-4136-B272-AA9BB4BBEA05@microsoft.com...
> Hello,
>
> I have a spreadsheet that basically holds date values in one column going
> down.
>
> Basically what i want to do is say "If the date in a cell is 1 day away
then
> turn the cell fill color red, if it's 2-3 days away turn it yellow"
>
> Is there a one liner that can be used as a global statement within the
sheet
> to evaluate all cells on the sheet and change color for those that satisfy
> the condition or must this be done cell by cell?
>
> Please help

What if every once and a while a row needed to be added, will the conditional
formatting be automatically inserted into the new cell? Can you do that with
some code? or would i have to copy the formatting over everytime a new row is
inserted?

"Tom Ogilvy" wrote:

> You could do it with conditional formatting.
>
> Select all the affected cells, then do Data=>Formatting => conditional
> formatting.
>
> Change cell Value is to Formula is
>
> (assume A1 is the active cell in the selection - you set up the formula
> relative to the active cell and use relative and absolute references as
> appropropriate)
>
> =(A1-today())<=1.5
>
> Select the format button and select the red pattern
>
> add a second condition
>
> =(A1-today())<3.5
>
> select the format button and select the yellow pattern
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Adam" <Adam@discussions.microsoft.com> wrote in message
> news:C1BE7FE4-0043-4136-B272-AA9BB4BBEA05@microsoft.com...
> > Hello,
> >
> > I have a spreadsheet that basically holds date values in one column going
> > down.
> >
> > Basically what i want to do is say "If the date in a cell is 1 day away
> then
> > turn the cell fill color red, if it's 2-3 days away turn it yellow"
> >
> > Is there a one liner that can be used as a global statement within the
> sheet
> > to evaluate all cells on the sheet and change color for those that satisfy
> > the condition or must this be done cell by cell?
> >
> > Please help
>
>
>

I believe if you have xl2000 or later and have it set to Extend Data Range
Formats and Formulas in Tools=>Options=> Edit tab, it will automatically
apply the conditional formatting.

If not, you can use code like this:

Sub Add_A_Row()
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.FillDown
ActiveCell.Offset(1, 0).EntireRow.ClearContents
End Sub

Unlike a straight insert, this would add a row below the selected cell.

--
Regards,
Tom Ogilvy


"Adam" <Adam@discussions.microsoft.com> wrote in message
news:A6B3DC55-639E-4491-9614-C16F40A8DF93@microsoft.com...
> What if every once and a while a row needed to be added, will the
conditional
> formatting be automatically inserted into the new cell? Can you do that
with
> some code? or would i have to copy the formatting over everytime a new row
is
> inserted?
>
> "Tom Ogilvy" wrote:
>
> > You could do it with conditional formatting.
> >
> > Select all the affected cells, then do Data=>Formatting => conditional
> > formatting.
> >
> > Change cell Value is to Formula is
> >
> > (assume A1 is the active cell in the selection - you set up the formula
> > relative to the active cell and use relative and absolute references as
> > appropropriate)
> >
> > =(A1-today())<=1.5
> >
> > Select the format button and select the red pattern
> >
> > add a second condition
> >
> > =(A1-today())<3.5
> >
> > select the format button and select the yellow pattern
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > news:C1BE7FE4-0043-4136-B272-AA9BB4BBEA05@microsoft.com...
> > > Hello,
> > >
> > > I have a spreadsheet that basically holds date values in one column
going
> > > down.
> > >
> > > Basically what i want to do is say "If the date in a cell is 1 day
away
> > then
> > > turn the cell fill color red, if it's 2-3 days away turn it yellow"
> > >
> > > Is there a one liner that can be used as a global statement within the
> > sheet
> > > to evaluate all cells on the sheet and change color for those that
satisfy
> > > the condition or must this be done cell by cell?
> > >
> > > Please help
> >
> >
> >