Gantt Chart - Extra Condition

[shemayisroel]

Hi,

The below is a sample Gantt chart that I'm using, it's working fine, what I want to do is when the STATUS is completed (Column H) goes from a cross to a tick (I'm using the format of Wingdings 2 - cross is O and tick is P) is to change the Gantt chart colour from it's current colour of green to say orange. In conditional formatting (in cell I3) I have, formula is =AND(I$2>=$F3,I$2<=$G3).

Is there a way I can add an additional conditional format or would this be a VBA? Any help would be appreciated.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD></TR><TR style="HEIGHT: 56px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">ITEM</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">WHO</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">DAYS</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Start</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">End</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">STATUS</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">01/01/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">08/01/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">15/01/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">22/01/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">29/01/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">05/02/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">12/02/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">19/02/09</TD><TD style="FONT-SIZE: 9pt; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: center">26/02/09</TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">Structure Data</TD><TD style="FONT-SIZE: 7pt; TEXT-ALIGN: center">PP</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">01/01/09</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">08/01/09</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; FONT-FAMILY: Wingdings 2; TEXT-ALIGN: center">O</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">Coding</TD><TD style="FONT-SIZE: 7pt; TEXT-ALIGN: center">TR</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">15/01/09</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">29/01/09</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #339966; FONT-FAMILY: Wingdings 2; TEXT-ALIGN: center">P</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #339966"> </TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">AUT</TD><TD style="FONT-SIZE: 7pt; TEXT-ALIGN: center">SM</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">21</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">15/01/09</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">05/02/09</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; FONT-FAMILY: Wingdings 2; TEXT-ALIGN: center">O</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 20px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">Live Roll Out</TD><TD style="FONT-SIZE: 7pt; TEXT-ALIGN: center">PW</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">42</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">15/01/09</TD><TD style="FONT-SIZE: 8pt; TEXT-ALIGN: center">26/02/09</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; FONT-FAMILY: Wingdings 2; TEXT-ALIGN: center">O</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 7pt; COLOR: #ff0000; BACKGROUND-COLOR: #99cc00"> </TD></TR></TBODY></TABLE>

 

[xenou]

Hmm, well specifically (XL2003)

In Cell I3:
1) Format | Conditional Formatting...
2) Formula Is
3) enter formula as above
4) Copy format as needed

Actually if you just test it in a cell first to see if it returns true or false thats better - put the formula in once you know the trues and falses are right. I gave two because I don't know if these are capital p's or small p's.

Does that work? Alex.

 

[shemayisroel]

Hmm, well specifically (XL2003)

In Cell I3:
1) Format | Conditional Formatting...
2) Formula Is
3) enter formula as above
4) Copy format as needed

Actually if you just test it in a cell first to see if it returns true or false thats better - put the formula in once you know the trues and falses are right. I gave two because I don't know if these are capital p's or small p's.

Does that work? Alex.

Hi,

Appreciate all your time & effort thus far, unfortunately that wont work as I already have conditional formatting in there being, example in I2, formula is =AND(I$2>=$F3,I$2<=$G3). As this is controlled from the start column (F) and end column (G), the Gantt chart works by when you update the dates the colours will fill. I used this method http://www.mrexcel.com/tip058.shtml

Any further help?

 

[jindon]

Re your PM

If you want to overwrite the interior color which are coloured via CF, I don't think it is possible.
Otherwise, everything should be done by the vba code, I'm afraid...

 

[shemayisroel]

Re your PM

If you want to overwrite the interior color which are coloured via CF, I don't think it is possible.
Otherwise, everything should be done by the vba code, I'm afraid...

Ok if I'm out of options via the CF then VBA it is, could you take a look at it?

Thanks.

 

[jindon]

What is the condition(s) applied the range I:Q ?

 

[shemayisroel]

What is the condition(s) applied the range I:Q ?

Example is I3 I have =AND(I$2>=$F3,I$2<=$G3)

Colour green

This condition is copied over all to the other cells

 

[jindon]

So that was the Gant Chart part....
Delete that condition and try

Private dic As Object
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, i As Long, myColor As Long
With Target
    If (.Column <> 8) + (.Row < 3) + (.Count > 1) Then Exit Sub
    If dic Is Nothing Then GenerateDic
    myColor = IIf(.Value = "O", vbGreen, vbMagenta)
    Rows(.Row).Interior.ColorIndex = xlNone
    Range(Cells(.Row, dic(.Offset(,-2).Value)), Cells(.Row, dic(Offset(,-1).Value))).Interior.Color = myColor
End With
End Sub
 
Private Sub GenerateDic()
Dim a, i As Long
Set dic = CreateObject("Scripting.Dictionary")
a = Range("i2", Cells(2, Columns.Count).End(xlToLeft)).Value
For i = 1 To UBound(a, 2)
    dic(a(1, i)) = i + 8
Next
End Sub
 
Private Sub Worksheet_Deactivate()
Set dic = Nothing
End Sub

 

[shemayisroel]

Hi jindon,

Thanks but I got a compile error: sub or function not defined

 

[jindon]

can you change

If dic Is Nothing Then GenerateDic

to

If dic Is Nothing Then Me.GenerateDic

 

[shemayisroel]

can you change
to

If dic Is Nothing Then Me.GenerateDic

Tried that

Error is now

"Method or data member not found"