Conditional Formatting for a Gantt Chart Type Schedule

[MntnrMark]

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!

 

[DRSteele]

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

 

[MntnrMark]

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]

What version of Excel are using?

 

[MntnrMark]

What version of Excel are using?

Microsoft Excel for Microsoft 365 MSO 64-bit.

 

[DRSteele]

Try to put the formula in cell C4 and copy across then down. What does the formula report?

 

[MntnrMark]

Try to put the formula in cell C4 and copy across then down. What does the formula report?

 

[DRSteele]

Well there - it's working as intended. All you need do is create a new Conditional Formatting rule using that formula.

 

[MntnrMark]

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]

Super! You're welcome.

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