Formula or VBA code to format certain cells based on the value.

[Seeker2025]

Good day to everyone!

I am looking for a formula that will apply conditional formatting to a certain based on the value in that cell.

I want to create a job pickup time tracker for three shifts in each sheet each day, each with different operators, and upon the duration of the job and the region number of cells to be highlighted.

Example1

Operator-1 picks a 1-hour duration job, and the job is from the UK region

The value I enter is 1-UK1234 were

1 is the 1-hour duration of the job
UK - Is the UK region code
1234 - Is the Job ID which is not standard

Then four cells to be highlighted considering each cell a 15-minute duration.

I have also attached the template and given sample data in this and manually highlighted the cells

But I would like to have a VBA code to highlight as many cells depend on the duration and region.

Pls, refer to my attached screenshot. I wanted to attach the excel workbook, but I think only screenshot I can attach.

I want green color for the UK region jobs, Blue for the US region jobs, Orange for the AP region jobs, Yellow for Break, and Red for Leave.

Cheers.

 

[Peter_SSs]

Thanks for the sample. You could try Conditional Formatting like this.
In the mini sheet below, I copied your first 3 sample rows down to rows 8:10 for comparison and just applied my CF to B3:AG6. You would need to apply it to your full data regions.

Seeker2025_1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		Morning Shift
2	Name	06:01 - 06:15	06:16 - 06:30	06:31 - 06:45	06:46 - 07:00	07:01 - 07:15	07:16 - 07:30	07:31 - 07:45	07:46 - 08:00	08:01 - 08:15	08:16 - 08:30	08:31 - 08:45	08:46 - 09:00	09:01 - 09:15	09:16 - 09:30	09:31 - 09:45	09:46 - 10:00	10:01 - 10:15	10:16 - 10:30	10:31 - 10:45	10:46 - 11:00	11:01 - 11:15	11:16 - 11:30	11:31 - 11:45	11:46 - 12:00	12:01 - 12:15	12:16 - 12:30	12:31 - 12:45	12:46 - 13:00	13:01 - 13:15	13:16 - 13:30	13:31 - 13:45	13:46 - 14:00
3	Name1	1-UK1111				0.75-US1234			0.5BR		0.5-AP3456		2-AP1234								3-UK9876												0.25BR
4	Name2	4-US5567																1-AP1122				1-UK0987				1BR
5	Name3	L
6	Name4
7	Name5
8	Name6	1-UK1111				0.75-US1234			0.5BR		0.5-AP3456		2-AP1234								3-UK9876												0.25BR
9	Name7	4-US5567																1-AP1122				1-UK0987				1BR
10	Name8	L
11	Name9
Mon

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AG6	Expression	=LOOKUP("zzz",$B3:B3)="L"	text	NO
B3:AG6	Expression	=LET(last,LOOKUP("zzz",$B3:B3),AND(RIGHT(last,2)="BR",COLUMNS($B:B)-XMATCH(last,$B3:B3,,-1)<LEFT(last,LEN(last)-2)*4))	text	NO
B3:AG6	Expression	=LET(last,LOOKUP("zzz",$B3:B3),AND(MID(last,FIND("-",last)+1,2)="AP",COLUMNS($B:B)-XMATCH(last,$B3:B3,,-1)<TEXTBEFORE(last,"-")*4))	text	NO
B3:AG6	Expression	=LET(last,LOOKUP("zzz",$B3:B3),AND(MID(last,FIND("-",last)+1,2)="US",COLUMNS($B:B)-XMATCH(last,$B3:B3,,-1)<TEXTBEFORE(last,"-")*4))	text	NO
B3:AG6	Expression	=LET(last,LOOKUP("zzz",$B3:B3),AND(MID(last,FIND("-",last)+1,2)="UK",COLUMNS($B:B)-XMATCH(last,$B3:B3,,-1)<TEXTBEFORE(last,"-")*4))	text	NO

 

[Seeker2025]

Thank you very much, Peter,
I applied the formatting that you have given. It seems only break time is working. I have given you the link below.

Could you please help me with what mistake I am making?

PickupTracker.xlsx

docs.google.com

 

[Peter_SSs]

Could you please help me with what mistake I am making?

Did you do this test with Office 365 per your profile?

 

[Seeker2025]

I checked at home in office 2021.

Will check at office in office 365 and get back to you.

Thank you once again.

 

[Peter_SSs]

I checked at home in office 2021.

The suggestion was written for Office 365 and will not work for Excel 2021 but possibly could be modified if it had to work in another version.

 

[Seeker2025]

The suggestion was written for Office 365 and will not work for Excel 2021 but possibly could be modified if it had to work in another version.

Thank you Peter,

You made my day........

It worked at office. Thank you very much.

May I get back to you if I will have any further implement.

 

[Peter_SSs]

It worked at office. Thank you very much.

You are welcome. Thanks for the confirmation.

May I get back to you if I will have any further implement.

If any further questions are closely related to this one, post again in this thread. Otherwise, start a new thread.

 

[Seeker2025]

You are welcome. Thanks for the confirmation.



If any further questions are closely related to this one, post again in this thread. Otherwise, start a new thread.

Hi Peter,

Sorry to bother you again,

I presented the report to my boss and she is pleased with that.

But she had one change in that. Kindly let me know whether it will be possible or not.

Just assume an operator had given 3 hours UK job at 6 am. So based on the current scenario, it is highlighted cells in green from 6 am to 9 am (B4:M3).

But if the operator wanted to go for a break in between the job, say for 30 minutes at 8 am. Then the job duration should get extended up to 9:30 am (B3:O3)

I have attached the snip and link for your reference. I will be grateful for Your help.

Thank you.

Loading Google Sheets

 

[Peter_SSs]

I have not thought much about this yet, but for clarification:
Is it only a "BR" that could interrupt a job or

1. Could the operator put one job aside part-way though and do another job then return to the original job?
2. Could there be a short "Leave" break to interrupt a job?
3. Could a job be interrupted by two or more short breaks?

 

[Seeker2025]

Yes, all the three questions were relevant.

However, my boss has not yet asked for these conditions to be implemented.

It would be a great help if you could also implement those conditions.

or at least questions 2 and 3.