3 Conditional Formatting in one cell

[MTownsend]

I am having trouble adding conditional formatting to one cell. I have developed an attendance Grid. This is how we want it to function:


When column C "Accumulated points" reaches 20, highlight column C Yellow.
Column H and J or manual fields.
When column C reaches column H's number + 4 Column C Turns Orange
When column C reaches Column J's number + 4 Column C Turns Red

I have tried several different way to make this happen and in testing, it does not work properly.

Anyone have any ideas?

Copy of 02022024 Test Workbook Updatesv3.xlsx
	A	B	C				G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
17	Employee ID Number	Employee Name	Accumulated Points				Date First Written Presented	Total Points on First Written	Date Second Written Presented	Total Points on Second Written	1/1	1/2	1/3	1/4	1/5	1/8	1/9	1/10	1/11	1/12	1/15	1/16	1/17	1/18	1/19	1/22	1/23	1/24	1/25	1/26
18	123456	Doe, Jane	0
19			0
20			0
Log

Cell Formulas
Range	Formula
C18:C20	C18	=SUM(K18:NM18)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C18:C513	Cell Value	=0	text	NO
C18:C513	Expression	=D18=5	text	NO
C18:C513	Expression	=D18=2	text	NO
C18:C513	Expression	=C18>19.99	text	NO

 

[dreid1011]

Perhaps these:

Book1
	A	B	C	D	E	F	G	H	I	J
17	Employee ID Number	Employee Name	Accumulated Points				Date First Written Presented	Total Points on First Written	Date Second Written Presented	Total Points on Second Written
18	123456	Doe, Jane	20
19			14					10
20			19							15
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C18:C20	Expression	=$C18=($J18+4)	text	NO
C18:C20	Expression	=$C18=($H18+4)	text	NO
C18:C20	Expression	=$C18=20	text	NO

Not sure why you have the rules looking at column D when you said column C... that may be why they're not working for you?

 

[MTownsend]

I used the formula's you provided: if you enter 4 into any of the date columns, Column C turns red. The way we would like it to function is once accumulated points hit 20, it turns yellow and should remain yellow until accumulated points = E+4 then it should turn orange and remain orange until accumulated points = G+4.

Attendance Tracker 2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
12	Employee ID Number	Employee Name	Accumulated Points	Date First Written Presented	Total Points on First Written	Date Second Written Presented	Total Points on Second Written	03/29/2024	03/30/2024	03/31/2024	04/01/2024	04/02/2024
13	123456	Doe, Jane	0
Log

Cell Formulas
Range	Formula
C13	C13	=SUM(H13:NK13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C13	Expression	=$C13=$G13+4	text	NO
C13	Expression	=$C13=$E13+4	text	NO
C13	Expression	=$C13=20	text	NO

 

[dreid1011]

Okay, give these a try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
12	Employee ID Number	Employee Name	Accumulated Points	Date First Written Presented	Total Points on First Written	Date Second Written Presented	Total Points on Second Written	45380	45381	45382	45383	45384
13	123456	Doe, Jane	24		22		26	10	4	10	0
14			26		22		26	10	5	7	4
15			29		22		26	12	12	5
16			30		22		26	13	13	4
17			19		22		26	15	4
Sheet1

Cell Formulas
Range	Formula
C13:C17	C13	=SUM(H13:NK13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C13:C17	Expression	=AND($C13>20,$C13>=$G13+4)	text	NO
C13:C17	Expression	=AND($C13>20,$C13>=$E13+4,$C13<$G13+4)	text	NO
C13:C17	Expression	=AND($C13<$E13+4,$C13>=20)	text	NO

 

[MTownsend]

If you remove the total points for first written and total points for second written, Accumulated points should remain yellow and it does not. If no points are in E or G, it should remain yellow until points are entered into E and then G.

 

[dreid1011]

If you remove the total points for first written and total points for second written, Accumulated points should remain yellow and it does not. If no points are in E or G, it should remain yellow until points are entered into E and then G.

Okay, I think I got it this time:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
12	Employee ID Number	Employee Name	Accumulated Points	Date First Written Presented	Total Points on First Written	Date Second Written Presented	Total Points on Second Written	45380	45381	45382	45383	45384
13	123456	Doe, Jane	30		22		26	10	4	10	6
14			26		22		26	10	5	7	4
15			29				26	12	12	5
16			30		22			13	13	4
17			25					15	10
Sheet1

Cell Formulas
Range	Formula
C13:C17	C13	=SUM(H13:NK13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C13:C17	Expression	=AND($C13>20,$C13>=$G13+4,$G13>0)	text	NO
C13:C17	Expression	=AND($C13>=$E13+4,OR($C13<$G13+4,$G13="",$G13=0),$E13>0)	text	NO
C13:C17	Expression	=AND($C13>=20,OR($C13<$E13+4,$E13="",$E13=0,$G13="",$G13=0))	text	NO

 

[MTownsend]

THANK YOU SO VERY MUCH @dreid1011

You are truly amazing and thank you for sticking with me!!!!!

 

[dreid1011]

THANK YOU SO VERY MUCH @dreid1011

You are truly amazing and thank you for sticking with me!!!!!

You're welcome, and happy to help.