Highlight cells using VBA

[Paul365]

Hello,

I was wondering if there is a possibility that VBA can be used to highlight cells, if a cell figure matches another. or a conditional formatting formula

Example (Using Time)
A2 - A 70 is marked (A2)7:00am, (A3)7:15 am, (A4)7:30 am etc.
If I insert a time in a different sheet (lets say sheet 2)- I would like the same cells to highlight in-between times.
Example
Cell A2 on sheet 2 - I type (Start time)8:00 am - and B1 I type (Finish Time)4:00 pm
I would Like Sheet 1 to highlight a color from A6 to A38 automatically

 

[DanteAmor]

Try:
On Sheet2:

Dante Amor
	A	B
1	Start Time	Finish Time
2	08:00	16:00
Sheet2

Cell A2 must have the range name "s_time" and cell B2 must have the range name "f_time"

Conditional format on Sheet1:

Dante Amor
	A
2	07:00
3	07:15
4	07:30
5	07:45
6	08:00
7	08:15
8	08:30
9	08:45
10	09:00
11	09:15
12	09:30
13	09:45
14	10:00
15	10:15
16	10:30
17	10:45
18	11:00
19	11:15
20	11:30
21	11:45
22	12:00
23	12:15
24	12:30
25	12:45
26	13:00
27	13:15
28	13:30
29	13:45
30	14:00
31	14:15
32	14:30
33	14:45
34	15:00
35	15:15
36	15:30
37	15:45
38	16:00
39	16:15
40	16:30
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A156	Expression	=AND($A2>=s_time,$A2<=f_time)	text	NO

 

[Paul365]

Try:
On Sheet2:

Dante Amor
	A	B
1	Start Time	Finish Time
2	08:00	16:00
Sheet2

Cell A2 must have the range name "s_time" and cell B2 must have the range name "f_time"

View attachment 79472

View attachment 79474

Conditional format on Sheet1:

Dante Amor
	A
2	07:00
3	07:15
4	07:30
5	07:45
6	08:00
7	08:15
8	08:30
9	08:45
10	09:00
11	09:15
12	09:30
13	09:45
14	10:00
15	10:15
16	10:30
17	10:45
18	11:00
19	11:15
20	11:30
21	11:45
22	12:00
23	12:15
24	12:30
25	12:45
26	13:00
27	13:15
28	13:30
29	13:45
30	14:00
31	14:15
32	14:30
33	14:45
34	15:00
35	15:15
36	15:30
37	15:45
38	16:00
39	16:15
40	16:30
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A156	Expression	=AND($A2>=s_time,$A2<=f_time)	text	NO

Hey, Thanks for this.

It works to a point, but if I change the time, it does not always highlight a certain time - example, if I put 8:00 am - 1a:00 am - It only highlights upto 10:45am

Any suggestions on how to get that extra cell highlighted?

Thanks

 

[DanteAmor]

It works to a point, but if I change the time, it does not always highlight a certain time - example, if I put 8:00 am - 1a:00 am - It only highlights upto 10:45am

Check my formula carefully, you didn't put "<=".

 

[Paul365]

Hey, thanks for your quick reply - I re-did the formula, and still have the same result.

 

[DanteAmor]

I don't see your formula, I'm not sure if you did the correction correctly.

The formula works:

 

[Paul365]

Amazing - It works, I had to re make the sheet to get it to work, but thanks so much.

Thanks