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!
 

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 
Solution

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
Joined
Feb 4, 2021
Messages
8
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.
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
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 13, 2021
Messages
159
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Feb 4, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

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

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