VBA: Highlight row within range if a date is a weekend or based on cell value

[IIII]

Hi,

I'm looking to find a way to highlight rows within this range (not entire row), based on the following two conditions:

1. If any date in column A is a weekend, then highlight that particular row and/or
2. If any cell value in Column B at min. contains the word "TEST" (doesn't have to match case) then highlight that particular row.

Example of output:

Book2
	A	B	C	D	E
1	Date	Shift	Hours	Kms	Status
2	Wednesday, 6 April 2022	Jane Doey	123	34	Approved
3	Wednesday, 6 April 2022	Jane Doey	123	34	Approved
4	Thursday, 7 April 2022	TEST EAGLES	123	34	Approved
5	Thursday, 7 April 2022	Alice Doey	123	34	Approved
6	Friday, 8 April 2022	Jane Doey	123	34	Approved
7	Saturday, 9 April 2022	TEST EAGLES	123	34	Approved
8	Saturday, 9 April 2022	Derek Doey	123	34	Approved
9	Monday, 11 April 2022	Jane Doey	123	34	Approved
10	Monday, 11 April 2022	TEST EAGLES	123	34	Approved
11	Tuesday, 12 April 2022	Jane Doey	123	34	Approved
12	Wednesday, 13 April 2022	Steve Doey	123	34	Approved
13	Wednesday, 13 April 2022	Jane Doey	123	34	Approved
14	Thursday, 14 April 2022	Jane Doey	123	34	Approved
15	Thursday, 14 April 2022	John Doey	123	34	Approved
16	Friday, 15 April 2022	John Doey	123	34	Approved
17	Saturday, 16 April 2022	John Doey	123	34	Approved
Sheet1

I'm able to highlight the individual cells easy enough for the second condition but just can't seem to get my head around doing the rest.

Thanks in advance for any help provided and please let me know if further info is required.

Cheers.

 

[Fluff]

How about

++Fluff.xlsm
	A	B	C	D	E
1	Date	Shift	Hours	Kms	Status
2	06/04/2022	Jane Doey	123	34	Approved
3	06/04/2022	Jane Doey	123	34	Approved
4	07/04/2022	TEST EAGLES	123	34	Approved
5	07/04/2022	Alice Doey	123	34	Approved
6	08/04/2022	Jane Doey	123	34	Approved
7	09/04/2022	TEST EAGLES	123	34	Approved
8	09/04/2022	Derek Doey	123	34	Approved
9	11/04/2022	Jane Doey	123	34	Approved
10	11/04/2022	TEST EAGLES	123	34	Approved
11	12/04/2022	Jane Doey	123	34	Approved
12	13/04/2022	Steve Doey	123	34	Approved
13	13/04/2022	Jane Doey	123	34	Approved
14	14/04/2022	Jane Doey	123	34	Approved
15	14/04/2022	John Doey	123	34	Approved
16	15/04/2022	John Doey	123	34	Approved
17	16/04/2022	John Doey	123	34	Approved
Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E17	Expression	=ISNUMBER(SEARCH("test",$B2))	text	NO
A2:E17	Expression	=WEEKDAY($A2,2)>5	text	NO

 

[IIII]

Hi @Fluff - Thank you for your response. I was hoping to get that same solution using VBA.

Apologies if that wasn't clear in my initial post.

Cheers.

 

[Fluff]

In that case just use the macro recorder as you add the CF rules & you will have the code.

 

[IIII]

In that case just use the macro recorder as you add the CF rules & you will have the code.

Ahh fair enough - not sure why I didn't think of that. Cheers.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kokano90]

Try it

and