Conditional Formatting

[gregmpenn]

I understand the basics that are built into Excel, but I have never tried to create my own out of the box conditional formatting.
In my example I have column (R1) set to =(Today).
In column (H) and (I) I have expected delivery dates.
What I am looking for is if column (H) or (I) are equal to todays date column (R1) then highlight those cells yellow.
If column (H) or (I) are prior to todays date column (R1) then highlight those cells Red.
Last but not lease if column (L) has a date, then remove the highlighted color(s) from column (H) or (I).

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

5/1/2023 0:00​

SMD

SSF

Work Week

Origin

Blocks

Size

Slice report

# of slices

Date shipped

Due in SSF ~ approx

Due in WA ~ approx

Incoming Tracking #

Ship Carrier

Received Date

Recvd Initials

Day Received

Seed Instructions

Process Priority Order

Notes

17

478

40

20

slice

209

04/25/2023

04/28/2023

29012298

Malca-Amit

Not_Rcvd

Smart Loading

3

17

482

30

20

slice

162

04/25/2023

04/28/2023

29012299

Malca-Amit

Not_Rcvd

Smart Loading

Redirect and ship to WA. To arrive in WA 4/29

17

483

39

20

slice

134

04/25/2023

04/28/2023

29012297

Malca-Amit

Not_Rcvd

Smart Loading

Redirect and ship to WA. To arrive in WA 4/29

17

499

40

20

slice

291

04/25/2023

04/28/2023

29012300

Malca-Amit

Not_Rcvd

Smart Loading

2

17

521

40

20

slice

300

04/25/2023

04/28/2023

29012304

Malca-Amit

Not_Rcvd

1

Large Seeds. Proces ASAP

18

490

40

20

slice

239

04/26/2023

05/02/2023

29012317

Malca-Amit

Not_Rcvd

18

491

40

20

slice

29012327

Malca-Amit

Not_Rcvd

18

502

40

20

slice

pending

Malca-Amit

Not_Rcvd

 

[awoohaw]

try this:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		SMD			If column (H) or (I) are prior to todays date column (R1) then highlight those cells Red. Last but not lease if column (L) has a date, then remove the highlighted color(s) from column (H) or (I).													5/1/2023 0:00
2		SSF
3	Work Week	Origin	Blocks	Size	Slice	# of slices	Date shipped	Due in SSF ~ approx	Due in WA ~ approx	Incoming	Ship Carrier	Received Date	Recvd Initials	Day Received	Seed	Process Priority Order	Notes
4					report					Tracking #					Instructions
5	17	478	40	20	slice	209	04/25/2023		04/28/2023	29012298	Malca-Amit	2023-05-01		Not_Rcvd	Smart Loading	3
6	17	482	30	20	slice	162	04/25/2023	04/28/2023		29012299	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
7	17	483	39	20	slice	134	04/25/2023	04/28/2023		29012297	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
8	17	499	40	20	slice	291	04/25/2023		04/28/2023	29012300	Malca-Amit	2023-05-04		Not_Rcvd	Smart Loading	2
9	17	521	40	20	slice	300	04/25/2023		04/28/2023	29012304	Malca-Amit			Not_Rcvd		1	Large Seeds. Proces ASAP
10	18	490	40	20	slice	239	04/26/2023		05/02/2023	29012317	Malca-Amit			Not_Rcvd
11	18	491	40	20	slice					29012327	Malca-Amit			Not_Rcvd
12	18	502	40	20	slice					pending	Malca-Amit	2023-05-08		Not_Rcvd
13
gregmpenn

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO
H5:H12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO

 

[Joe4]

Do the following:

For yellow highlighting:
1. Select columns H and I
2. Go to Conditional Formatting
3. Select "New Rule"
4. Select "User a formula to determine which cells to format"
5. Enter the following formula:

=AND($L1="",H1=$R$1)

6. Click the format button
7. Go to the Fill tab and select the yellow formatting color
8. Click OK

For red highlighting:
Follow same steps as above but use this formula:

=AND($L1="",$H1>0,$H1<$R$1)

and select red fill color.

 

[awoohaw]

try this:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		SMD			If column (H) or (I) are prior to todays date column (R1) then highlight those cells Red. Last but not lease if column (L) has a date, then remove the highlighted color(s) from column (H) or (I).													5/1/2023 0:00
2		SSF
3	Work Week	Origin	Blocks	Size	Slice	# of slices	Date shipped	Due in SSF ~ approx	Due in WA ~ approx	Incoming	Ship Carrier	Received Date	Recvd Initials	Day Received	Seed	Process Priority Order	Notes
4					report					Tracking #					Instructions
5	17	478	40	20	slice	209	04/25/2023		04/28/2023	29012298	Malca-Amit	2023-05-01		Not_Rcvd	Smart Loading	3
6	17	482	30	20	slice	162	04/25/2023	04/28/2023		29012299	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
7	17	483	39	20	slice	134	04/25/2023	04/28/2023		29012297	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
8	17	499	40	20	slice	291	04/25/2023		04/28/2023	29012300	Malca-Amit	2023-05-04		Not_Rcvd	Smart Loading	2
9	17	521	40	20	slice	300	04/25/2023		04/28/2023	29012304	Malca-Amit			Not_Rcvd		1	Large Seeds. Proces ASAP
10	18	490	40	20	slice	239	04/26/2023		05/02/2023	29012317	Malca-Amit			Not_Rcvd
11	18	491	40	20	slice					29012327	Malca-Amit			Not_Rcvd
12	18	502	40	20	slice					pending	Malca-Amit	2023-05-08		Not_Rcvd
13
gregmpenn

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO
H5:H12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO

ignore this, I omitted the yellow formatting Sorry.

 

[gregmpenn]

try this:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		SMD			If column (H) or (I) are prior to todays date column (R1) then highlight those cells Red. Last but not lease if column (L) has a date, then remove the highlighted color(s) from column (H) or (I).													5/1/2023 0:00
2		SSF
3	Work Week	Origin	Blocks	Size	Slice	# of slices	Date shipped	Due in SSF ~ approx	Due in WA ~ approx	Incoming	Ship Carrier	Received Date	Recvd Initials	Day Received	Seed	Process Priority Order	Notes
4					report					Tracking #					Instructions
5	17	478	40	20	slice	209	04/25/2023		04/28/2023	29012298	Malca-Amit	2023-05-01		Not_Rcvd	Smart Loading	3
6	17	482	30	20	slice	162	04/25/2023	04/28/2023		29012299	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
7	17	483	39	20	slice	134	04/25/2023	04/28/2023		29012297	Malca-Amit			Not_Rcvd	Smart Loading		Redirect and ship to WA. To arrive in WA 4/29
8	17	499	40	20	slice	291	04/25/2023		04/28/2023	29012300	Malca-Amit	2023-05-04		Not_Rcvd	Smart Loading	2
9	17	521	40	20	slice	300	04/25/2023		04/28/2023	29012304	Malca-Amit			Not_Rcvd		1	Large Seeds. Proces ASAP
10	18	490	40	20	slice	239	04/26/2023		05/02/2023	29012317	Malca-Amit			Not_Rcvd
11	18	491	40	20	slice					29012327	Malca-Amit			Not_Rcvd
12	18	502	40	20	slice					pending	Malca-Amit	2023-05-08		Not_Rcvd
13
gregmpenn

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO
H5:H12	Expression	=AND(L5="",OR(AND(I5<$R$1,I5<>""),AND(H5<$R$1,H5<>"")))	text	NO

Your solution worked perfect, thank you so much. For those on this forum that respond it is greatly appreciated. I know your doing the work for me, but please know I take what you did and I study it so I can learn and ask less questions as I go.