Conditional Formatting for a Gantt Chart Type Schedule

MntnrMark

New Member
Joined
Dec 18, 2012
Messages
40
1613690808368.png


I want to use Conditional Formatting to format the cells above with an " x " in them to instead shade/fill the same color (say, light blue) throughout the range C4 through L7. I tried using an AND statement using the 'Use a formula . . . ' formatting rule, such as =AND($A4>$D2, and I don't know how to complete the formula. How do I do this? (NOTE: There are hundreds of rows and columns in the file I'm working in; this is just an example. For those familiar with Gantt chart scheduling, I basically want to show the months where there is activity occurring in a month with shading.) Thanks for the assistance!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
I understand from what you tried to say that you want the cells C4:L7 to have a blue fill when the dates in Start and Finish coincide with the dates in C2:L2. For example, there was an X in cell F4 because there was activity in October/21.

If that is so, highlight C4 to L7 and paste this into the New Rule box and pick a format (like blue fill).

=AND(C$2>=(EOMONTH($A4,-1)+1),C$2<=(EOMONTH($B4,0)))
 
Solution

MntnrMark

New Member
Joined
Dec 18, 2012
Messages
40
I understand from what you tried to say that you want the cells C4:L7 to have a blue fill when the dates in Start and Finish coincide with the dates in C2:L2. For example, there was an X in cell F4 because there was activity in October/21.

If that is so, highlight C4 to L7 and paste this into the New Rule box and pick a format (like blue fill).

=AND(C$2>=(EOMONTH($A4,-1)+1),C$2<=(EOMONTH($B4,0)))
Hmmm. That's not doing it. I'm not getting anything in C4 through L7 at all. I triple checked the formula. Any thoughts on why that isn't working?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are using?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
Try to put the formula in cell C4 and copy across then down. What does the formula report?
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
Well there - it's working as intended. All you need do is create a new Conditional Formatting rule using that formula.
 

MntnrMark

New Member
Joined
Dec 18, 2012
Messages
40
Well there - it's working as intended. All you need do is create a new Conditional Formatting rule using that formula.
OK, now its working. For the life of me I don't know why it wasn't working earlier, but it is now. THANK YOU SO MUCH!
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,353
Office Version
  1. 365
Platform
  1. Windows
Super! You're welcome.

Conditional Formatting can be irritating. When you get a spare hour, watch this video from ExcelIsFun to expand your skillset.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,116
Messages
5,622,816
Members
415,934
Latest member
adstocking

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