Gantt Chart- colouring bars

1988craig

New Member
Joined
Aug 17, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I have used excels template 'Gantt project planner'.
I have multiple trades on the sheet & I am trying to colour the bars different colours for the retrospective trade.

Can I edit the existing conditional formatting to differentiate between the trades, and if so could you please advise how?

I am not using the features in the template for tracking- actual start & progress etc only the planned start date.

Please see the example, many thanks & very much appreciated for the assistance.


Screen Shot 2020-10-31 at 7.32.10 pm.png
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

1988craig

New Member
Joined
Aug 17, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Post updated- many thanks

Gantt project planner example.xlsx
CDEFGHIJNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
5
6Period Highlight:week 1week 1
7Start02/11/2020PLAN STARTPLAN DURATIONstart dateStart Time02/11/202003/11/202004/11/202005/11/202006/11/202007/11/202008/11/2020
9End15/01/2021tradeMondayTuesdayWednesdayThursdayFridaySaturdaySunday
1112345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
12Trade 13202/11/202008:00:00
13Trade 2381615/01/202108:00:00
14Trade 32402/11/202007:00:00
15Trade 453812/11/202009:00:00
16Trade 14202/11/202009:00:00
17Trade 24302/11/202009:00:00
18Trade 340409/11/202010:00:00
19Trade 45202/11/202010:00:00
20Trade 15202/11/202010:00:00
21Trade 26502/11/202011:00:00
22Trade 36102/11/202011:00:00
23Trade 48303/11/202006:00:00
Project Planner
Cell Formulas
RangeFormula
E7E7=MIN(I12:I43)
U7,BD7,AW7,AP7,AI7,AB7U7=N7+1
N9,BD9,AW9,AP9,AI9,AB9,U9N9=N7
E9E9=MAX(I13:I44)
I12:I23I12=VLOOKUP(G12,Sheet1!$A$2:$C$2430,3,FALSE)
J12:J23J12=VLOOKUP(G12,Sheet1!$A$2:$C$2430,2,FALSE)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U7:AYJ10Expression=WEEKDAY(U$8,2)>5textNO
U11:AYJ11Expression=WEEKDAY(U$8,2)>5textNO
U12:AYJ43Expression=PercentCompletetextNO
U12:AYJ43Expression=PercentCompleteBeyondtextNO
U12:AYJ43Expression=ActualtextNO
U12:AYJ43Expression=ActualBeyondtextNO
U12:AYJ43Expression=PlantextNO
U12:AYJ43Expression=U$4=period_selectedtextNO
U11:AYJ11Expression=U$11=period_selectedtextNO
U12:AYJ43Expression=MOD(COLUMN(),2)textNO
U12:AYJ43Expression=MOD(COLUMN(),2)=0textNO
N7:T10Expression=WEEKDAY(N$8,2)>5textNO
N11:T11Expression=WEEKDAY(N$8,2)>5textNO
N12:T43Expression=PercentCompletetextNO
N12:T43Expression=PercentCompleteBeyondtextNO
N12:T43Expression=ActualtextNO
N12:T43Expression=ActualBeyondtextNO
N12:T43Expression=PlantextNO
N12:T43Expression=N$4=period_selectedtextNO
N11:T11Expression=N$11=period_selectedtextNO
N12:T43Expression=MOD(COLUMN(),2)textNO
N12:T43Expression=MOD(COLUMN(),2)=0textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,126,977
Messages
5,621,935
Members
415,867
Latest member
mauroccs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top