Conditional formatting depending on cell values

fable

New Member
Joined
Nov 16, 2012
Messages
24
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,620
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJ
1StartEnd
21/12/20134/5/20141/10/20131/15/20132/3/201312/1/20131/1/20143/1/20146/1/2014
31/1/20132/4/20131/11/20131/24/20132/1/20132/5/20135/1/20135/2/20135/3/2013
411/1/20132/4/20141/12/201310/2/201310/31/201311/1/201312/1/20131/30/20143/24/2014
Sheet1
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,620
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 16, 2012
Messages
24

ADVERTISEMENT

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

Board Regular
Joined
Jun 22, 2008
Messages
74
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,620
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
BCDE
1StartEnd
21/12/20134/5/2014Formula
31/1/20132/4/2013=$C2
411/1/20132/4/2014
58/1/20138/22/2013
6
Sheet
 

fable

New Member
Joined
Nov 16, 2012
Messages
24
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

Board Regular
Joined
Jun 22, 2008
Messages
74
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,109,120
Messages
5,526,935
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top