Looking for VBA Code to Conditionally Format Time Tracker on Entry

[Tara_A_Data]

Hi There, I am wondering if someone could at all assist.

I am trying to locate a VBA code to generate a type of conditional formatting of coloured cells based on the content which holds various times.

This is a sample of a Staff Time Tracker that I need this to work on. I had previously set it fully in just Conditional Formatting but when I sort the data it threw all the formatting off as it was only cell specific.

There is a main column of cells B2:B7 with the time that shows the start time of the staff members lateness time, as you can see below there is a list of various times for different staff. What I am hoping to achieve is where the time is shown in Column B - this would be the starting point of the staff lateness time and if there was any possibility to pull that time through when data is added into the cells D2:Q7 so that the timing as per below will show up in Red (Color Index = 3) as they are late on each day. So anytime from that listing time in column B and onwards would need to show in Red.

However, if the times are <than the shown times in column B or the cell is listed with a Y these need to remain as a white colourless cells.

Hopefully this make sense as I really hope someone can help?

Thank you @Tara_A_Data

​

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

Full name​

Start​

Staff Status​

02 September 2024​

03 September 2024​

04 September 2024​

05 September 2024​

06 September 2024​

09 September 2024​

10 September 2024​

11 September 2024​

12 September 2024​

13 September 2024​

16 September 2024​

17 September 2024​

18 September 2024​

19 September 2024​

2​

Mr Smith

07:23​

Business Manager

Y​

07:20​

07:23​

07:00​

Y​

Y​

07:35​

08:15​

09:00​

10:50​

Y​

07:00​

Y​

10:50​

3​

Miss John

07:23​

Kitchen

10:50​

07:00​

07:12​

Y​

07:45​

Y​

07:35​

07:10​

07:35​

07:23​

07:45​

10:50​

07:45​

07:35​

4​

Ms Clapham

07:23​

Administrator

Y​

Y​

10:50​

07:00​

07:35​

07:45​

10:50​

Y​

07:45​

Y​

08:10​

08:50​

Y​

10:50​

5​

Miss Twickenham

07:23​

Reception

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

10:00​

6​

Mr Chelsea

07:37​

Reception

07:37​

08:00​

07:30​

07:35​

07:38​

07:40​

07:45​

07:58​

07:00​

07:10​

Y​

06:55​

Y​

Y​

7​

Dr Cornwashy

07:52​

Data Manager

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

11:00​

10:00​

07:00​

07:52​

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings.


Try this conditional formatting, I did tests and you can sort the data and the format is not lost:

varios 24sep2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Full name	Start	Staff Status	02 sep 2024	03 sep 2024	04 sep 2024	05 sep 2024	06 sep 2024	07 sep 2024	08 sep 2024	09 sep 2024	10 sep 2024	11 sep 2024	12 sep 2024	13 sep 2024	14 sep 2024	15 sep 2024
2	Mr Smith	07:23	Business Manager	Y	07:20	07:23	07:00	Y	Y	07:35	08:15	09:00	10:50	Y	07:00	Y	10:50
3	Miss John	07:23	Kitchen	10:50	07:00	07:12	Y	07:45	Y	07:35	07:10	07:35	07:23	07:45	10:50	07:45	07:35
4	Ms Clapham	07:23	Administrator	Y	Y	10:50	07:00	07:35	07:45	10:50	Y	07:45	Y	08:10	08:50	Y	10:50
5	Miss Twickenham	07:23	Reception	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	10:00
6	Mr Chelsea	07:37	Reception	07:37	08:00	07:30	07:35	07:38	07:40	07:45	07:58	07:00	07:10	Y	06:55	Y	Y
7	Dr Cornwashy	07:52	Data Manager	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	11:00	10:00	07:00	07:52
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:Q9	Expression	=D2+0>$B2	text	YES

But if you prefer, try the following macro:

Sub CodeToConditionalFormatting()
  Cells.FormatConditions.Delete
  With Range("D2:Q9")   'Adjust the range as you wish
    .FormatConditions.Add xlExpression, , "=D2+0>$B2"
    .FormatConditions(1).Interior.ColorIndex = 3
  End With
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Tara_A_Data]

​

Y​

Y​

Y​

Y​

​

 

[Tara_A_Data]

Dear @DanteAmor,

Thank you, for your warm welcomes to this forum.

I also would like to say a massive Thank you for the VBA code - it works perfectly. Its great and yes it does also sort the data without losing any of the formatting. I very much appreciate your help with this and will keep that VBA code very safe and use it further.

Thank you again, have a great day.
@Tara_A_Data