Conditional Formatting with Ghantt Chart- Copying to additional cells

[justme]

Good morning everyone!
In row 5 I have calendar dates, in columns D & E I have projected start & end dates.
What I am trying to do is create a ghantt chart that will color in the dates in the row between the projected start/end dates listed in columns D & E.
I have the following conditional format in cell G8: =IF(AND(G5>=$D8), (G5<=$E8)). Works great. Now, how do I extend this conditional formula to include up to row 100 and to columns FV? I certainly don't want to format each cell! Thank you in advance!

 

[Joe4]

If you select the WHOLE range you want to apply the Conditional Formatting to, and then write the Conditional Formatting formula as it applies to the very FIRST cell in your selected range, then if you have used absolute/relative range referencing correctly, Excel will automatically adjust the CF formula for all other cells in your selected range.

I always do this when I have to apply Conditional Formatting to a multi-cell range, and it works well, as long as I have written my formula correctly.

 

[justme]

Thank you for your reply Joe. I did try that, but the problem is this. Even without using the absolute references, the formula does not "advance" beyond G8,it only works properly in g8. And in the capture below I get some odd ball formatting in cells I10-I13. I can change the date in cell G5 to 9-15-23 and G8 formats correctly

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3			Project Start:	Mon, 8/28/2023
4			Display Week:	1			August
5							28	29	30	31	1	2	3
6	TASK	PHASE	ASSIGNED TO	START	END		M	T	W	T	F	S	S
7	3033 Haas 5 axis
8	Project 1	Pre-production	TBD	9/11/23	9/26/23
9		Production		9/27/23	10/14/23
10	Project 2	Pre-production	TBD	10/14/23	11/3/23
11		Production		11/4/23	12/3/23
12	Project 3	Pre-production	TBD	12/4/23	12/18/23
13		Production		12/19/23	12/28/23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:I13	Expression	=IF(G5>=D8,AND(G5<=E8))	text	NO

 

[Joe4]

You need to put the "$" in front of the row and columns that you want to lock down.
Since you always want to look at row 5 in the header, you want to lock that part down.
And since you always want to look columns D and E in your formula, you want to lock that part down.

Also, I am not sure why you have an IF there. I think all you need is an AND function (AND functions return boolean values of TRUE or FALSE).

So I think your formula needs to look like this:

=AND(G$5>=$D8,G$5<=$E8)

See here for a more detailed explanation on mixed cell references: Mixed Reference Excel - a Beginner's Guide.

 

[justme]

THANK YOU!!! Absolutely perfect!
Happy Labor Day weekend to you and all the other hard workers out there.

 

[justme]

I'm sorry, one more question. Is there a way to format the color differently for Production vs Pre-production?

 

[Joe4]

I'm sorry, one more question. Is there a way to format the color differently for Production vs Pre-production?

Sure. You would just create two rules, and have a different color for each.

So, for Production, you would just add one more condition:

=AND(G$5>=$D8,G$5<=$E8,$B8="Production")

and for Pre-production:
So, for Production, you would just add one more condition:

=AND(G$5>=$D8,G$5<=$E8,$B8="Pre-production")

 

[justme]

Again, thank you. I was on the right path but......

 

[Joe4]

You are welcome.
Glad I was able to help!