conditional format??

[JOEE1979]

I'm seriously stuck on this one.
The column "N" is times my drivers are supposed to be at their cars.
The column is highlighted yellow, showing me how many more runs my drivers have for the day.
In N3 I want to put a number (for example 4) that's how many trucks I have ready to be sent out.
How would I be able to use the number in "N3" and it will take the yellow highlights and make them green letting me know that my shipments are safe until a certain time?
Keep in mind that I have a top section and a bottom section, but I need them both to work together.
Thanks

Weekday Bus Cycling - copy.xlsx
	N
1
2	Sign on
3
4	10:05
5	15:00
6	16:00
7	18:00
8
9	20:00
10
11
12	4
13
14
15	Sign on
16
17	11:35
18	12:02
19	12:20
20	12:25
21	16:00
22	17:00
23	18:00
24
25
26	12:00
27	16:00
28	19:00
29
30	5
31
32
Steeprock

Cell Formulas
Range	Formula
N12	N12	=COUNTIF(N4:N11,">="&$A$1)
N30	N30	=COUNTIF(N17:N29,">="&$A$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D11,N4:N11,D17:D29,N17:N29	Cell Value	>MOD(NOW(),1)	text	NO

 

[MARK858]

This will not work as the "10" is not a constant and will change depending on the # of trucks I have ready to go

Then see the conditional format formula in the post 7 based on cell A2 value

 

[JOEE1979]

Then see the conditional format formula in the post 7 based on cell A2 value

I'm trying to put that into my sheet, only problem is that I have 4 ranges to pick data from
d4:d26, d32:d69, n4:n26, n32:n69
when I combine those into your suggestion, it didnt work
***follow the sheet in post 5****

 

[MARK858]

AFAIK you can't use a formula with non-contiguous ranges in conditional formatting (you can use the built in function and edit the value)

 

[JOEE1979]

AFAIK you can't use a formula with non-contiguous ranges in conditional formatting (you can use the built in function and edit the value)

Is there a way to make a condition based on the color of the cell? Then we can come up with a formula to change the lowest "X" amount of "yellow" cells in a range to "Green"

 

[MARK858]

Is there a way to make a condition based on the color of the cell?

Not without using vba

 

[JOEE1979]

Not without using vba

I'm ok with VBA, I'm not very well experienced, would you please help me?

 

[MARK858]

Not tonight as it is the early hours of the morning here and I am at work tomorrow.

You will also need all your cells in yellow done by VBA as the conditional formatting you have takes priority over any fill colour we put in via VBA.

It is much easier just to do the clicks (or ALT + H, L, R to get you part way there) and change the number in the formatting rules

 

[JOEE1979]

Sorry, Let me explain it better.
$D$1 =MOD(NOW(),1)
What I want to do is have a number in Cell "A2". That number would be how many trucks are ready to hit the road, (ie 10)
I want a way to take the "10" lowest numbers that are Highlighted in yellow (they have a conditional format already to make them yellow) and make them green

Weekday Bus Cycling - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	MDDS5115	SPLT	muwtf	6:10	6:25	8:55	DDS	98.677		1	MDDS5121	REL	muwtf	10:05	10:20	20:20	DDS	394.644
5	MDDS5113	SPLT	muwtf	5:55	6:10	9:50	DDS	125.874		2	MDDS5122	REL	muwtf	10:40	10:55	19:55	DDS	328.82
6	MDDS5101	REG	muwtf	2:25	2:40	10:50	DDS	300.625		3	MDDS5124	REL	muwtf	11:50	12:05	21:50	DDS	409.936
7	MDDS5107	REG	muwtf	4:50	5:05	11:10	DDS	222.347		4	MDDS5125	SPLT	muwtf	12:05	12:20	17:45	DDS	227.905
8	MDDS5118	SPLT	muwtf	6:55	7:10	11:30	DDS	137.672		5	MDDS5126	REL	muwtf	12:19	12:34	23:45	DDS	449.661
9	MDDS5102	REG	muwtf	2:25	2:40	12:05	DDS	442.896		6	MDDS5127	SPLT	muwtf	12:50	13:05	18:30	DDS	223.105
10	MDDS5112	REG	muwtf	5:40	5:55	12:25	DDS	227.914		7	MDDS5128	REL	muwtf	13:20	13:35	23:10	DDS	350.172
11	MDDS5106	REG	muwtf	4:25	4:40	13:05	DDS	330.075		8	MDDS5129	REL	muwtf	13:30	13:45	22:15	DDS	255.073
12	MDDS5109	REG	muwtf	4:55	5:10	13:15	DDS	267.443		9	MDDS5130	REL	muwtf	13:35	13:50	23:43	DDS	437.086
13	MDDS5103	REG	muwtf	3:25	3:40	13:20	DDS	420.212		10	MDDS5131	REL	muwtf	13:55	14:10	21:40	DDS	296.312
14	MDDS5104	REG	muwtf	4:05	4:20	13:50	DDS	412.598		11	MDDS5132	REL	muwtf	14:05	14:20	0:25	DDS	406.034
15	MDDS5105	REG	muwtf	4:20	4:35	13:55	DDS	318.807		12	MDDS5133	REL	muwtf	14:20	14:35	22:15	DDS	307.042
16	MDDS5114	REG	muwtf	6:00	6:15	14:10	DDS	213.239		13	MDDS5134	REL	muwtf	14:30	14:45	21:15	DDS	210.293
17	MDDS5108	REG	muwtf	4:55	5:10	14:20	DDS	420.212		14	MDDS5135	REL	muwtf	14:45	15:00	22:45	DDS	220.381	RPT
18	MDDS5110	REG	muwtf	5:05	5:20	14:30	DDS	284.54		15	MDDS5136	REL	muwtf	15:05	15:20	0:25	DDS	339.3
19	MDDS5111	REG	muwtf	5:40	5:55	15:20	DDS	374.472		16	MDDS5137	REL	muwtf	16:15	16:25	3:10	DDS	436.021
20	MDDS5116	REG	muwtf	6:20	6:35	18:10	DDS	428.417		17
21	MDDS5117	REG	muwtf	6:35	6:50	18:35	DDS	402.575		18
22	MDDS5119	REG	muwtf	7:05	7:20	18:55	DDS	427.629		19
23	MDDS5120	REG	muwtf	7:35	7:50	19:55	DDS	404.488		20
24										21	MDDS5123	REL	muwtf	10:45	2:00	20:35	DDS	349.439
25
26
27				3		20								17		17
Steeprock

Cell Formulas
Range	Formula
D27,P27,N27,F27	D27	=COUNTIF(D4:D26,">="&$D$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P4:P26,F4:F26,P32:P69,F32:F69,P73:P1048576,F73:F1048576	Cell Value	>$D$1	text	NO
N4:N26,D4:D26,N32:N69,D32:D69,N73:N1048576,D73:D1048576	Cell Value	>$D$1	text	NO

How can I combine my already used conditional format with a "SMALL" format to highlight the smaller cells in a different color