conditional formatting help

[karldiffenderfer]

Hello,

I posted a question last night but see that I have one piece to finish
my spreadsheet.

Here it is...

I have a Gantt Chart that I am developing in excel. Basically I have
conditional formatting that compares the date range of a particular
task to the actual date and thus fills in small excel squares showing
visually how things are progressing in the timeline. It shows in
little green squares When the task starts and when it is supposed to
end.

Here is a screenshot of what it looks like.

http://download.shireinteractive.com/images

So basically as you can see on row 31 I have the text grayed out
because 'X" is in the field B31. I would like to apply that same
grayed out effect to the green date range on the right in row31.

Currently that date range code looks like this.

=IF(AND(V$3>=$E13,V$3<=$F13),1,"")

There is then a conditional formatting applied to that cell telling it
that if it equals "1" to make that cell green.

Right now anything that is not in the date range is white, Anything
that is in the date range is green. I want to add one more component,
anything that is in the date range and has an "x" in the B column in
that row, I want it to turn that green date range to Gray.

Is this possible?

If this doesn't make sense please let me know and I will try to explain
in a different manner.

I have also posted the excel spreadsheet in that link above for your
convenience.

As always thanks for your time.

 

[Bob Phillips]

I have updated your file to show how to do it. It is a zip file, as it was
too big to upload it vanilla. Get it at :http://cjoint.com/?kupXBMVtGM

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sandy]

Try this:
Hit add in your conditional formatting window for the cell (it will
bring up condition 2) and enter in using the formula is type and type:
=V$3="x" in the text box and then set the backround to grey

 

[Bernie Deitrick]

Karl,

I would try this for the Green:

=AND(V3>=$E3,V3<=$F3,$B3<>"X")

and this for the grey

=AND(V3>=$E3,V3<=$F3,$B3="X")


Both of these should be applied when cell V3 is the activecell - I'm not sure about your key date
structure, since I don't download unknown spreadsheets. But I've assumed that they key dates are in
the same row, as opposed to off by 10 (E3 vs. E13)

HTH,
Bernie
MS Excel MVP

 

[PCLIVE]

Condition 1 for Green:
=AND(W$3>=$E31,W$3<=$F31)

Condition 2 for Gray:
=AND(AND(W$3>=$E31,W$3<=$F31),B31="x")

If you do not do any calculations on all the ones (1) that your current
conditional formatting looks at, you can get rid of those formulas and just
use the Conditional formatting above.

Regards,
Paul

 

[PCLIVE]

Oops! That order should be reversed.
Condition 1 for Gray:
=AND(AND(W$3>=$E31,W$3<=$F31),B31="x")

Condition 2 for Green:
=AND(W$3>=$E31,W$3<=$F31)

 

[karldiffenderfer]

Hello Bob,

This works except for one thing. I only want the date range designated
in columns E and F to be grayed over in the "green gantt chart area"
but not in the rest of the row. That way we still see our Gantt chart
but get to see the grayed areas as complete. The other cells out side
that date range designated by E and F should be the white/no color
background.

Make sense?

Thanks again for all your help.

 

[karldiffenderfer]

I got it working!!!

Thanks again. I greatly appreciate it!