Conditional formatting referencing multiple cells.
 

Good day,

Hoping someone can help…

In cell C4 I have percentage
In cell E4 I have a due date
In cell F4 I have calculated the number of days something is overdue
using this formula:
=DAYS360(TODAY(),E4)

What I would like to be able to do, is highlight the cells A4 to F4 if
C4 is less than 100 and F4 is less than 1.

Many thanks to whomever can help!

Conditional formatting referencing multiple cells.
 

Select the *entire* range A4:F4 starting from cell A4. Cell A4 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

In Excel 2007

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


"Bullocks" wrote in message
...
Good day,

Hoping someone can help…

In cell C4 I have percentage
In cell E4 I have a due date
In cell F4 I have calculated the number of days something is overdue
using this formula:
=DAYS360(TODAY(),E4)

What I would like to be able to do, is highlight the cells A4 to F4 if
C4 is less than 100 and F4 is less than 1.

Many thanks to whomever can help!

Conditional formatting referencing multiple cells.
 

On Nov 27, 12:03*pm, "T. Valko" wrote:
Select the *entire* range A4:F4 starting from cell A4. Cell A4 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

In Excel 2007

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP

"Bullocks" wrote in message

...
Good day,

Hoping someone can help…

In cell C4 I have percentage
In cell E4 I have a due date
In cell F4 I have calculated the number of days something is overdue
using this formula:
=DAYS360(TODAY(),E4)

What I would like to be able to do, is highlight the cells A4 to F4 if
C4 is less than 100 and F4 is less than 1.

Many thanks to whomever can help!

You're a hero!

Conditional formatting referencing multiple cells.
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Bullocks" wrote in message
...
On Nov 27, 12:03 pm, "T. Valko" wrote:
Select the *entire* range A4:F4 starting from cell A4. Cell A4 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

In Excel 2007

Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format
Enter this formula in the box below:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

In Excel versions 2003 and earlier

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP

"Bullocks" wrote in message

...
Good day,

Hoping someone can help…

In cell C4 I have percentage
In cell E4 I have a due date
In cell F4 I have calculated the number of days something is overdue
using this formula:
=DAYS360(TODAY(),E4)

What I would like to be able to do, is highlight the cells A4 to F4 if
C4 is less than 100 and F4 is less than 1.

Many thanks to whomever can help!

You're a hero!

Conditional formatting referencing multiple cells.
 

On Fri, 27 Nov 2009 12:03:47 -0500, "T. Valko"
wrote:

=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)

sorry to interrupt, but
why the AND?

Conditional formatting referencing multiple cells.
 

=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)
why the AND?

Because we're testing for multiple conditions and they *all* must be TRUE
for the format to be applied.

First we check to make sure there are in fact numbers entered in the 2
cells:

COUNT($C4,$F4)=2 = TRUE or FALSE

If both cells were empty then:

$C4<100% = TRUE
$F4<1 = TRUE

And in that case the format would be applied but I'm pretty sure that is not
what the OP wants to happen.

So, with AND each condition must be TRUE for the format to be applied.

=AND(TRUE,TRUE,TRUE) = TRUE

Any other combination of TRUE/FALSE = FALSE

--
Biff
Microsoft Excel MVP


"Domanda" wrote in message
...
On Fri, 27 Nov 2009 12:03:47 -0500, "T. Valko"
wrote:

=AND(COUNT($C4,$F4)=2,$C4<100%,$F4<1)

sorry to interrupt, but
why the AND?

Conditional formatting referencing multiple cells.
 

CF/ Formula is:
=AND($C4<100,$F4<1)

As a matter of interest, did you actually intend DAYS360, or could you have
used =E4-TODAY() ?
--
David Biddulph

"Bullocks" wrote in message
...
Good day,

Hoping someone can help…

In cell C4 I have percentage
In cell E4 I have a due date
In cell F4 I have calculated the number of days something is overdue
using this formula:
=DAYS360(TODAY(),E4)

What I would like to be able to do, is highlight the cells A4 to F4 if
C4 is less than 100 and F4 is less than 1.

Many thanks to whomever can help!