Highlight Rows if today's date is between 2 column dates

[QueenS]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row Index
[/TD]
[TD]Column D
(Start Date)
[/TD]
[TD]Column E
(End Date)
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]20-JUL-2014
[/TD]
[TD]27-JUL-2014
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]01-OCT-2014
[/TD]
[TD]01-NOV-2014
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]07-OCT-2014
[/TD]
[TD]09-OCT-2014
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]09-OCT-2014
[/TD]
[TD]01-NOV-2014
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]14-AUG-2014
[/TD]
[TD]07-OCT-2014
[/TD]
[/TR]
</tbody>[/TABLE]

Seeking expert assistance in building a conditional format for above sample data

Objective: Using a macro or conditional format to highlight Rows B, C, and E if I open the spreadsheet today and today's date is 07-OCT-2014.


Grateful for any help.

 

[tusharm]

Select D2:E{whatever} and add a conditional format using a formula.

The formula should be =AND($D2<=TODAY(),$E2>=TODAY())

If you want to highlight the entire row, select row 2:{whatever} before adding the c.f.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row Index
[/TD]
[TD]Column D
(Start Date)
[/TD]
[TD]Column E
(End Date)
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]20-JUL-2014
[/TD]
[TD]27-JUL-2014
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]01-OCT-2014
[/TD]
[TD]01-NOV-2014
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]07-OCT-2014
[/TD]
[TD]09-OCT-2014
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]09-OCT-2014
[/TD]
[TD]01-NOV-2014
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]14-AUG-2014
[/TD]
[TD]07-OCT-2014
[/TD]
[/TR]
</tbody>[/TABLE]

Seeking expert assistance in building a conditional format for above sample data

Objective: Using a macro or conditional format to highlight Rows B, C, and E if I open the spreadsheet today and today's date is 07-OCT-2014.


Grateful for any help.

 

[Mike54]

How about this

=IF(AND(TODAY()>=(B2),TODAY()<=(C2)),5)

You can use this with the "Use Formula" in conditional formatting

Mike

 

[ztodd]

You can use this for your conditional formatting conditon: =and($D2<=today(),today()<=$E2) applied to your whole range of data.
That's assuming your data starts in row 2.

 

[QueenS]

Many thanks to tusharm, Mike54 and ztodd for responding. Recommended formula works fine!