conditional formatting - multiple conditions in gantt chart

debbw

New Member
Joined
Feb 4, 2021
Messages
16
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!
 
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.
 
Upvote 0
Solution

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top