# conditional formatting - multiple conditions in gantt chart

#### debbw

##### New Member
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!

#### JamesCanale

##### Board Regular
MrExcelPlayground.xlsx
CDEFGHIJKLMNOPQR
4StartEndDuration1234567891011
5F2/1/20212/3/20213FFF
6O2/4/20212/5/20212   OO
7O2/8/20212/9/20212       OO
8F2/10/20212/11/20212         FF
Sheet11
Cell Formulas
RangeFormula
G5:G8G5=F5-E5+1
H5:R8H5=IF(AND(\$E5<=H\$4,\$F5>=H\$4),IF(\$C5="F","F","O"),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H5:R8Cell Value="O"textNO
H5:R8Cell Value="F"textNO

I've done things before like this - except in the conditional formatting, make the foreground and background the same color if you don't want to see the letters.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### debbw

##### New Member
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.
I tried the Add-on, but kept locking up... An alternate option was posted by another member & it seems to be doing the trick, although, a little more involved than I wanted. It will work in the meantime.

Again - THANK YOU so much for helping

#### debbw

##### New Member
MrExcelPlayground.xlsx
CDEFGHIJKLMNOPQR
4StartEndDuration1234567891011
5F2/1/20212/3/20213FFF
6O2/4/20212/5/20212   OO
7O2/8/20212/9/20212       OO
8F2/10/20212/11/20212         FF
Sheet11
Cell Formulas
RangeFormula
G5:G8G5=F5-E5+1
H5:R8H5=IF(AND(\$E5<=H\$4,\$F5>=H\$4),IF(\$C5="F","F","O"),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H5:R8Cell Value="O"textNO
H5:R8Cell Value="F"textNO

I've done things before like this - except in the conditional formatting, make the foreground and background the same color if you don't want to see the letters.
James - THANK YOU! This solution is a good alternative and will work for our current needs.
-Deb

#### JamesCanale

##### Board Regular
James - THANK YOU! This solution is a good alternative and will work for our current needs.
-Deb
Your welcome - I enjoy these problems - this site delights me. Google has been bringing me here for years, but I only joined last month.

#### debbw

##### New Member
Your welcome - I enjoy these problems - this site delights me. Google has been bringing me here for years, but I only joined last month.
Yes, I've visited several times after I attended a class YEARS ago & this site was recommended as an additional resource. I'll have to visit more frequently to see if I might be able to offer assistance to someone...

Replies
0
Views
180
Replies
10
Views
207
Replies
8
Views
39
Replies
22
Views
425
Replies
5
Views
155

Threads
1,129,568
Messages
5,637,097
Members
416,957
Latest member
Brovashift

### 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

### 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