Conditional Formatting Formula

[Falko26]

Hey Team,

I am looking to do some conditional formatting on a Gantt chart for Prerequisites. I am using the styles "Good Bad and Neutral" from the styles tab.

Columns D E F G are manually assigned a number. I want the formatting to look at the number assigned to the cell, find the row associated with that number from column "B", and Check Column "I" for the the % Complete. If the percent is Less than 90% I want the formatting to show up "Bad". If its between 90% and 99% I want it to show up Neutral. If its 100% complete then I want it to be "Good". I also am looking to get this applied to the entirety of columns D E F G so it will still work if rows are added. So we might need something that states if cell value does not have a number than do nothing with formatting? is that possible?

Thanks for the assistance.

 

[dreid1011]

Maybe something like this:

Select the entire column range D:G and apply the formulas. You'll have to change the lookup ranges though to handle adding rows.

Book1
	B	C	D	E	F	G	H	I
9	1		2	3	4	5		10%
10	2		6					20%
11	3		7	8				30%
12	4		9	10	11			40%
13	5		12	13				50%
14	6		14					60%
15	7		15					70%
16	8							80%
17	9							90%
18	10							90%
19	11							90%
20	12							0%
21	13							0%
22	14							100%
23	15							100%
24	16							0%
25	17							0%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:G	Expression	=XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)=1	text	NO
D:G	Expression	=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)>0.89,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<1)	text	NO
D:G	Expression	=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)>0,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<0.9)	text	NO

 

[Falko26]

Thanks so Much!

This is 99% what I'm looking for.

Is there any way to also color prerequisites red if the percentage = 0? Without causing the rest of the cells in the columns turning red since 0 also applies to blank cells. I tried changing it to >= Greater than or equal to and this obviously happened.

Thanks,

 

[dreid1011]

Yes, see the new rule here:

Book1
	B	C	D	E	F	G	H	I
9	1		2	3	4	5		10%
10	2		6					20%
11	3		7	8				30%
12	4		9	10	11			40%
13	5		12	13				50%
14	6		14					60%
15	7		15					70%
16	8							80%
17	9							90%
18	10							90%
19	11							90%
20	12							0%
21	13							0%
22	14							100%
23	15							100%
24	16							0%
25	17							0%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:G	Expression	=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)=0,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<>"")	text	NO
D:G	Expression	=XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)=1	text	NO
D:G	Expression	=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)>0.89,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<1)	text	NO
D:G	Expression	=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)>0,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<0.9)	text	NO

=AND(XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)=0,XLOOKUP(D1,$B$9:$B$25,$I$9:$I$25,"",0)<>"")

 

[Falko26]

Perfect!
Thanks so much

 

[dreid1011]

Perfect!
Thanks so much

You're welcome, and happy to help.