Conditional formatting for range of cells?

[Guest]

Cells B2 to B150 contain dates. Is it possible to apply conditional
formatting to B1 so that it turns red if any of the dates in B2:B150 are
greater than TODAY()? If so, what is the formula?

TIA
David

 

[Guest]

Try this:

Select cell B1

Then, from the Excel main menu:
<format><conditional formatting>
Condition 1: Click the drop down and set:
Formula is: =COUNTIF($B$2:$B$150,">"&TODAY())
Click the [format..] button and set the format you want
Click the [OK] buttons to finish

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks, Ron. This does exactly what I asked for. After entering it, I
realized that I need to refine the requirements.

Column B contains the date that a documents was sent out for review. In
Column C I enter the date that the reviewed document was returned. To
visually flag if a document has not been returned within 14 days, I have
applied conditional formatting to the individual cells of column C as follows:

=AND(C2="",TODAY()>B2+14) format RED

What I would like C1 to show is if any of the cells of column C is RED
without having to scroll all the cells. The last row presently is 150, but as
the worksheet grows, there will be more of a chance of missing a red cell.

Hope this is clear.

Regards,
David

Try this:

Select cell B1

Then, from the Excel main menu:
<format><conditional formatting>
Condition 1: Click the drop down and set:
Formula is: =COUNTIF($B$2:$B$150,">"&TODAY())
Click the [format..] button and set the format you want
Click the [OK] buttons to finish

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Cells B2 to B150 contain dates. Is it possible to apply conditional
formatting to B1 so that it turns red if any of the dates in B2:B150 are
greater than TODAY()? If so, what is the formula?

TIA
David

 

[Guest]

Try this CF formula for C1:

=SUMPRODUCT(((C2:INDEX(C:C,COUNTA(B:B)))=0)*(TODAY()>((B2:INDEX(B:B,COUNTA(B:B)))+14)))

(Note: That formula assumes that B1 contains a value.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks, Ron. This does exactly what I asked for. After entering it, I
realized that I need to refine the requirements.

Column B contains the date that a documents was sent out for review. In
Column C I enter the date that the reviewed document was returned. To
visually flag if a document has not been returned within 14 days, I have
applied conditional formatting to the individual cells of column C as follows:

=AND(C2="",TODAY()>B2+14) format RED

What I would like C1 to show is if any of the cells of column C is RED
without having to scroll all the cells. The last row presently is 150, but as
the worksheet grows, there will be more of a chance of missing a red cell.

Hope this is clear.

Regards,
David

Try this:

Select cell B1

Then, from the Excel main menu:
<format><conditional formatting>
Condition 1: Click the drop down and set:
Formula is: =COUNTIF($B$2:$B$150,">"&TODAY())
Click the [format..] button and set the format you want
Click the [OK] buttons to finish

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Cells B2 to B150 contain dates. Is it possible to apply conditional
formatting to B1 so that it turns red if any of the dates in B2:B150 are
greater than TODAY()? If so, what is the formula?

TIA
David

 

[Guest]

Works like a charm!! Thanks!

Just wondering, why does B1 have to contain a value? For the purposes of the
question, I simplified row and column numbers, but adjusted them in the CF
formula so that it works as required. In fact, my dates start in cells J6 and
K6. The first 11 rows and 9 columns contain other project related
information.

David

Try this CF formula for C1:

=SUMPRODUCT(((C2:INDEX(C:C,COUNTA(B:B)))=0)*(TODAY()>((B2:INDEX(B:B,COUNTA(B:B)))+14)))

(Note: That formula assumes that B1 contains a value.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks, Ron. This does exactly what I asked for. After entering it, I
realized that I need to refine the requirements.

Column B contains the date that a documents was sent out for review. In
Column C I enter the date that the reviewed document was returned. To
visually flag if a document has not been returned within 14 days, I have
applied conditional formatting to the individual cells of column C as follows:

=AND(C2="",TODAY()>B2+14) format RED

What I would like C1 to show is if any of the cells of column C is RED
without having to scroll all the cells. The last row presently is 150, but as
the worksheet grows, there will be more of a chance of missing a red cell.

Hope this is clear.

Regards,
David

Try this:

Select cell B1

Then, from the Excel main menu:
<format><conditional formatting>
Condition 1: Click the drop down and set:
Formula is: =COUNTIF($B$2:$B$150,">"&TODAY())
Click the [format..] button and set the format you want
Click the [OK] buttons to finish

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Cells B2 to B150 contain dates. Is it possible to apply conditional
formatting to B1 so that it turns red if any of the dates in B2:B150 are
greater than TODAY()? If so, what is the formula?

TIA
David

 

[Guest]

Actually, the key is to make sure that the row_ref in the INDEX function
reaches down far enough in your range to include the last items. You might
need to use something like this:

=SUMPRODUCT(((K6:INDEX(K:K,COUNTA(J:J))+5)=0)*(TODAY()>((J6:INDEX(J:J,COUNTA(J:J)+5))+14)))

Side note: It's usually best to present your exact situation and references
in your posted examples. There are times when the solution to the generic,
edited example is dramatically different from the final solution.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Works like a charm!! Thanks!

Just wondering, why does B1 have to contain a value? For the purposes of the
question, I simplified row and column numbers, but adjusted them in the CF
formula so that it works as required. In fact, my dates start in cells J6 and
K6. The first 11 rows and 9 columns contain other project related
information.

David

Try this CF formula for C1:

=SUMPRODUCT(((C2:INDEX(C:C,COUNTA(B:B)))=0)*(TODAY()>((B2:INDEX(B:B,COUNTA(B:B)))+14)))

(Note: That formula assumes that B1 contains a value.)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Thanks, Ron. This does exactly what I asked for. After entering it, I
realized that I need to refine the requirements.

Column B contains the date that a documents was sent out for review. In
Column C I enter the date that the reviewed document was returned. To
visually flag if a document has not been returned within 14 days, I have
applied conditional formatting to the individual cells of column C as follows:

=AND(C2="",TODAY()>B2+14) format RED

What I would like C1 to show is if any of the cells of column C is RED
without having to scroll all the cells. The last row presently is 150, but as
the worksheet grows, there will be more of a chance of missing a red cell.

Hope this is clear.

Regards,
David

:

Try this:

Select cell B1

Then, from the Excel main menu:
<format><conditional formatting>
Condition 1: Click the drop down and set:
Formula is: =COUNTIF($B$2:$B$150,">"&TODAY())
Click the [format..] button and set the format you want
Click the [OK] buttons to finish

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Cells B2 to B150 contain dates. Is it possible to apply conditional
formatting to B1 so that it turns red if any of the dates in B2:B150 are
greater than TODAY()? If so, what is the formula?

TIA
David