how do i flag a cell using background color

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

heather - 21 Aug 2006 21:02 GMT

I need to know how to change the background color of a cell based on data
entered. Such as a date that is more than a year the background of that cell
turn red.

Reply to this Message

WLMPilot - 21 Aug 2006 21:31 GMT

You want to use CONDITIONAL FORMATTING. After clicking on the cell you want
to check, open the conditional format window, change CELL VALUE IS to FORMULA
IS in the combo box. The you will have a space to enter the formula. If I
understand what you are trying to do, lets say A1 = 10/30/2007. Want to to
check that date against today's date and flag it if it is more than a year
away. If that is the case, then the formula you need to type in is:

=AND(A1<>"", A1>TODAY()+365)

Suggestion: If you are using a dark background, ie red, blue, black, etc, I
would change the font to white also.

Les

> I need to know how to change the background color of a cell based on data
> entered. Such as a date that is more than a year the background of that cell
> turn red.

Reply to this Message

JLatham - 21 Aug 2006 22:34 GMT

And if you need to go the other way with a date (is it more than 1 year in
the past), then again, use Format | Conditional Format

Choose [Cell Value Is] then in the condition choose Less Than
and in the formula area to the right, enter this
=(NOW()-365)
or, to be more precise
=(NOW()-365.25)
Set up your format. Now if the date is more than a year behind the current
date, it will get flagged. Obviously, once it gets that old, it's going to
stay that older as NOW() increases day by day.

NOW() returns the current system date/time, so it's always current, not
static on a worksheet.

> You want to use CONDITIONAL FORMATTING. After clicking on the cell you want
> to check, open the conditional format window, change CELL VALUE IS to FORMULA
[quoted text clipped - 13 lines]
> > entered. Such as a date that is more than a year the background of that cell
> > turn red.

Reply to this Message