conditional formatting - multiple conditions in gantt chart

debbw

New Member
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Greetings! I am trying to add a condition to a formula that will change the bar color based on referencing a cell value of "F" in Column c ($C7). This column allows the project manager a quick view to know who is "field" employee vs. "office" employee. I'm using Office 365.
The basic formula to select dates is:
=AND(task_end>=I$5,task_start<J$5)

Where & how do I incorporate "$c7="F"

Basically, I'm looking for: "IF column c ="F", then format the date range (task_end>=I$5,task_start<J$5) to my desired color.

I haven't been able to find this specific scenarion anywhere. Any help is GREATLY appreciated.
THANK YOU!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,990
Office Version
  1. 2016
Platform
  1. Windows
try this: =AND(task_end>=I$5,task_start<J$5, $c5-"F")
(I assume you mean row 5 not 7?)
 

debbw

New Member
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
try this: =AND(task_end>=I$5,task_start<J$5, $c5-"F")
(I assume you mean row 5 not 7?)
Thank you for trying, Peter. Unfortunately, that didn't work (I did the same thing). Here's a screenshot of that formula's results. The left side "assigned to" is column C (task_assigned to). The goal is to have the "green" bar for anything assigned to "F"

GC shot.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=AND(task_end>=I$5,task_start<J$5,$C7="F")
 

debbw

New Member
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=AND(task_end>=I$5,task_start<J$5,$C7="F")
Thank you, but I'm still getting mixed colors on the row, not specific to "F"
-Deb
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
What is the applies to range for that condition?
 

debbw

New Member
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the applies to range for that condition?
=$I$8:$BL$34
Capturing the full date range of the project
Previous range started at "J" & just noticed that was incorrect.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
In that case you need to use $C8="F"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
Can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,870
Members
417,174
Latest member
diegomuser

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