Conditional formatting depending on cell values

[fable]

Hey guys, seen as you were so efficient last time, was wondering if you might be able to help me once more.

Taking this down to brass tacks as it were, I have a table with two date columns, and I want excel to highlight all the dates in the same row that are between these values.

e.g

So each row will highlight the dates (D:I) within the start and end range (B:C) and vary themselves appropriately down the rows?

Pretty sure this is doable with just the conditional formatting, think I might just be being a touch slow about how to do it....

Any help would be appreciated!!

Thanks

~Fable

 

[AhoyNC]

Select your range D:I that you want to format.

Then choose the following options from the ribbon:
1- Conditional Formatting
2- Highlight Cells Rules
3- Between
4- In the fist box enter =$B2 in the second box enter =$C2 then choose formatting you want and press OK

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	Start	End
2	1/12/2013	4/5/2014	1/10/2013	1/15/2013	2/3/2013	12/1/2013	1/1/2014	3/1/2014	6/1/2014
3	1/1/2013	2/4/2013	1/11/2013	1/24/2013	2/1/2013	2/5/2013	5/1/2013	5/2/2013	5/3/2013
4	11/1/2013	2/4/2014	1/12/2013	10/2/2013	10/31/2013	11/1/2013	12/1/2013	1/30/2014	3/24/2014
Sheet1

 

[fable]

now... you know I swear I tried that! Nevermind. Thanks Ahoy!

 

[AhoyNC]

You're welcome.
One thing to check with conditional formatting is to go to Manage Rules, then Edit and make sure Excel didn't put quotation marks around your formula. Other thing that can cause problems is how you lock either rows or columns.

 

[fable]

The other thing I'm now attempting to do is to get excel to highlight the cells in column B & C where C is less than thirty days from today's date.

Now, I tried =$C2<=(TODAY()+30) but it's not quite what I'm looking for.

Any tips?

 

[bigroo]

Fable,

Then choose the following options from the ribbon:
1- Conditional Formatting
2- Use a Formula to determine which cells to format
3- Enter in =$C2<=(TODAY()+30)
4- Select your formatting


Try that!

 

[AhoyNC]

The other thing I'm now attempting to do is to get excel to highlight the cells in column B & C where C is less than thirty days from today's date.

Now, I tried =$C2<=(TODAY()+30) but it's not quite what I'm looking for.

Any tips?

What do you mean by "not quite what I'm looking for".

I tried your formula and it seems to work??

Excel Workbook
	B	C	D	E
1	Start	End
2	1/12/2013	4/5/2014		Formula
3	1/1/2013	2/4/2013		=$C2
4	11/1/2013	2/4/2014
5	8/1/2013	8/22/2013
6
Sheet

 

[fable]

Fable,

Then choose the following options from the ribbon:
1- Conditional Formatting
2- Use a Formula to determine which cells to format
3- Enter in =$C2<=(TODAY()+30)
4- Select your formatting


Try that!

Surprisingly that's exactly what I was doing?

And again -that exact formula works today, and didn't yesterday. How Bizarre.

Thanks again!

~F

 

[bigroo]

Well isn't that something!!
I am surprised it did not work!

 

[bigroo]

Fable,

How about this?
Select your range B:C that you want to format.

Then choose the following options from the ribbon:
1- Conditional Formatting
2- Format only Cells that contain
3- Cell Value
4- Less than or equal to
5- In the next field box enter =Today()-30 then choose formatting you want and press OK

Is this what you want?
Also:

The other thing I'm now attempting to do is to get excel to highlight the cells in column B & C where C is less than thirty days from today's date.

Now, I tried =$C2<=(TODAY()+30) but it's not quite what I'm looking for.

You mentioned you want less than 30 days from today's date, your example shows plus 30 days from today's date. You want less right??