Highlight a series of Cells, based on Date range

[IT Pete]

Hi Guys,

Help I'm stuck! What am I doing wrong?

I am working a project schedule in Excel and I would like to highlight the cells that correspond to the start date to the end date without manually having to do it. I have found other options in my search such as creating a bar graph, but don't suit our needs.

I even purchase a Gantt Project templates earlier today (on Chandoo webiste), but the Director's didn't like the layout's.

I have been trying to use; =AND($F$2>=$C$3,$F$2<=$D$3) , but, it keeps highlighting the whole row! Aggghhh...

Hope this explanation wasn't too confusing. I have uploaded a copy of the Excel spreadsheet I'm working on, so you can see where I am up to.

[ PSi Projects Track List 01 - Copy.xlsx ]

I would appreciate any help and advice, even if you feel I should change something slightly.

Thanks guys!! Going home now - I have a headache...

[UPDATE: 15 Jan 2015, 18:18 UK - Using Excel 2013]
I should have posted earlier; I was trying to setup up using Conditional Formatting and with the formula above, and I have tried '=AND(F$2>=$C3,F$2<=$D3)', which both have not worked for me.

 

[bobhc]

You really need to look at the CF rules when creating them, the reason it highlights all (most)of the row is because you have told it to do so. Look in the applies to widow in the Manage Rules box and you will see this.....=$F$3:$NF$3
It goes all the way up to NF3 a lot of row colour, a lot of memory for the unwanted CF.


.

 

[Somendra Misra]

Hi,

Use this:

=AND(F$2>=$C3,F$2<=$D3)

Regards,

 

[IT Pete]

You really need to look at the CF rules when creating them, the reason it highlights all (most)of the row is because you have told it to do so. Look in the applies to widow in the Manage Rules box and you will see this.....=$F$3:$NF$3
It goes all the way up to NF3 a lot of row colour, a lot of memory for the unwanted CF.

.

Hi Bobhc,

Sorry, I rush typed my Post - the office was closing and would have got locked in.

I should have said, I was trying to use the =AND($F$2>=$C$3,$F$2<=$D$3) , in CF and the Row area highlighted, I wanted the Cell to Fill In in say Black.

What I was actually doing was; [Using Excel 2013]
1) Highlight the Row area I wanted to apply the CF too = F3-NF3.
2) Goto CF > New Rule > Use a formula to Determine Which Cells To Format.
3) Enter '=AND($F$2>=$C$3,$F$2<=$D$3)' into the Format Values Where This Formula Is True.
4) Click on the Format... button and set my Fill Colour choice - say, Black.
5) Then click the OK button.
6) I go back into CF > Manage Rules, just to check settings.
7) If shows Applies To = =$F$3:$NF$3 .

I do understand, a lot of Row Colour, etc, but, I can only setup what I have been asked by my Director's.

But, I am open to slight change to the spreadsheet, if it would help.

And, I have suggested to the Director's at looking at purchasing a something like Microsoft Project, or similar, but, for what they want this spreadsheet for, MS Project, or other, would be overkill.

 

[IT Pete]

Hi,

Use this:

=AND(F$2>=$C3,F$2<=$D3)

Regards,

Hi Somendra,

Thanks for your reply.

I did actually try your suggestion '=AND(F$2>=$C3,F$2<=$D3)', before posting on Chandoo forums - and that didn't work for me either!

The formula I first posted, is what I found on another website forum and tried using that.

Oh bummer... what seemed like a fairly simple 'ish idea! I need a drink!

Many thanks for your suggestion though - any other idea's, would be gratefully received!!

 

[IT Pete]

Hi Guys,

Not been home long, just updating my original post and replying to some kind folks to have replied already.

Well, I have a sample copy of the spreadsheet (I posted here), at home with me, and I'm using Excel 2010 at home (office, we are using Excel 2013), and I got the formula '=AND(F$2>=$C3,F$2<=$D3)', which this version was also given to me by Somendra, and it works!!! Great!!! But, why???

I'm confused! Hmmm...

 

[Luke M]

You're original formula:
=AND($F$2>=$C$3,$F$2<=$D$3)
New formula:
=AND(F$2>=$C3,F$2<=$D3)

Note that the latter doesn't have as many "$" symbols, which are used for absolute references. The first formula, which uses all absolute references, will be TRUE if F2 is in the range, and doesn't care what cell the formatting is applied to. So, all the formatted cells will either be on, or all will be off.

The new formula uses relative references, so the top-left cell is looking at F2, C3, and D3. If you copy the formatting to the right, it looks at E2, C3, and D3. If you then copy down, it looks at E2, C4, and D4. Knowing how absolute/relative references work is the key here. Thus, each cell that gets this formatting will use a slightly different version of the formula to see if it should be highlighted.
http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/

 

[IT Pete]

Hi All,

Just wnated to say thanks for all your help and input! Sorry I haven't been on for a few days, been out-and-about on-site, on-site IT stuff on a couple of new projects just started.

I am pleased to say the Excel spreadsheet I wanted to acheive, has been acheived, with your helpful insights!

Thanks again Peep's!!