shemayisroel
Well-known Member
- Joined
- Sep 11, 2008
- Messages
- 1,867
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>
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>