Conditional Formatting based on content used with a Gantt Chart

[gwstudent]

I have a standard generic Excel Gantt chart that works perfectly fine that I would like to show different colored bars based on a name in one of the cells. Can anyone help.


A1, B1, C1,

xx, 8/1/2011, 8/15/2011

yy, 8/2/2011, 8/10/2011


If xx then show red bar, if yy then show blue bar, etc...

My standard grey bar conditional formula is =AND(Q$3>=$B1,Q$3<=$B1+$L1-1)

Q$3 = start date

$A1 = first cell of start date column

$L1 = no. of days to complete

 

[fred]

I'd suggest you do two chart overlaying one on top of the other. Set condition where only one graph would display.

 

[Hui]

Add another series to the chart


All values for this series will be 0 except where the bar is

the cell will have your formula to put a value in subject to the condition or otherwise a na()

color this series Red


In the existing series change the cells formula to do the opposite.

That is when the condition is met put a =na() otherwise put the value

Color this series Blue


Make sure the series are in the right order

 

[gwstudent]

Hui -


That makes perfect sense and that's exactly where I was heading. However, could you provide some insight into the logic I would use to look at the value in A1 to determine if a bar should display? Pseudo code would be something like:

If I find "xx" in Column A and the dates look good go ahead and display a blue bar

If I find "yy" in Column A and the dates look good go ahead and display a red bar (separate series)


Something like this

=IF(ISERROR(FIND(LOWER("xx"),LOWER(A1),1)),"No","Yes") + AND(Q$3>=$B1,Q$3<=$B1+$L1-1)


The logic of these two statements work independently - I need help with combining them.

 

[Hui]

Can you post your file somewhere ?

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[gwstudent]

Here you go: https://skydrive.live.com/?cid=564f9597e16a0def&sc=documents&id=564F9597E16A0DEF!116


You will see there are standard gray bars that display for each person in column D. I would like to colorize the bars to be specific to each person.

 

[Hui]

Can you please check the file permissions as I'm getting a

"An error has occurred. Please try again."

error


or email me

Email is at Bottom of Excel Ninja page, link under my Picture to the left

 

[gwstudent]

Thanks Hui. I just send you the file via email.

 

[Hui]

Wouldn't the easiest way be to just select N6:IR6 and apply the CF to that

then do Row 7 etc

Change Color of each CF to suit


or


Select N6:IR11

Change the GREY CF equation to =AND(N$3>=$E6,N$3<=$E6+$I6-1,MOD(ROW(),2)=1)

and add a new BLUE CF with equation =AND(N$3>=$E6,N$3<=$E6+$I6-1,MOD(ROW(),2)=0)