Conditional Formatting help, highlighting a range between 2 Values?

[Defski]

Hello,

I am hoping one of you Excel Masters can help me with my Conditional Formatting desirers.

What I would like to do is Highlight all the rows between 2 Values.

Currently I am using > Use a formula to determine which cells to format.

The formula I am using is =INDIRECT("R"& ROW())=$U$3 I then have it applying to =$A$7:$Z$1066.

This works great for part of what I would like to do, it takes the weight data I have in cell $U$3 and then highlights the part of whatever row that contains that data I need it to.

What I would like to do differently is use a second data point, the weight data I also have in cell $U$5 and then have it highlight all the rows between those two data points.

Is this something possible, it seems to me like it should be???

Thanks

 

[Fluff]

How about
=and($R7>=$U$3,$R7<=$U$5)

 

[Defski]

That did not appear to do anything?

 

[Fluff]

It should highlight all rows where the value in col R is greater or equal to U3 & less than or equal to U5.
Is that what you wanted?

 

[Defski]

Maybe its because I have several other conditional formats going on? Namely the one I mentioned earlier and another virtually the same only highlighting U5...

 

[Fluff]

Make sure that the new rule is at the top of the list & it should overrule all the other rules.

 

[Defski]

I did and I even tested deleting all the other rules.

 

[Fluff]

In that case you please supply a sample of your data, there is an add-in here that enables you to include a sample in your post XL2BB

 

[Defski]

Book1
	S
4	0.604
wk42HS

Cell Formulas
Range	Formula
B1	B1	=WEEKNUM(H2)
AA2	AA2	=(1-$R$1)*$V$3+($R$1*(S4*$V$3))*(1-$R$2)
AB2	AB2	=(1-$R$1)*$V$3+($R$1*(R4*$V$3))*(1-$R$2)
V3	V3	=U5
W3	W3	=AB2+$AB$4+(-AB2*$R$2)
X3	X3	=AA2+$AB$4+(-AA2*$R$2)
V4	V4	=VLOOKUP(V3+0.2,R11:R1769,1,TRUE)
W4:X4	W4	=VLOOKUP(W3+1,$T$11:$T$1769,1,TRUE)
AB3	AB3	=T3-AB4
AC3	AC3	=U4+AC4
AB4	AB4	=\[Reaction Calculation version1.8.xlsm]Reaction with Event (3)'!$J$28
AC4	AC4	=(U4-AB4)*(1*R2)
B4	B4	=RIGHT(YEAR(H2),1)&CHAR(MONTH(H2)+64)&E5&DAY(H2)&"AlN"&B1
R4	R4	=((1-$R$3)*(1-$R$1)+$R$3)
S4	S4	=((1-$S$3)*(1-$R$1)+$S$3)
T4	T4	=VLOOKUP(T3+0.1,T11:T1769,1,TRUE)
E5	E5	=IF(DAY(H2)<10,"0","")
U3	U3	=VLOOKUP(AC3,R10:R1258,1,TRUE)
U4	U4	=$AB$3/($R$4/1)
U5	U5	=$AB$3/($S$4/1)
S7:S9, S285, S283, S280:S281, S273:S274, S268, S267, S260:S261, S255, S253, S247, S245, S241, S240, S238, S233:S234, S231, S228, S224, S217:S218, S211, S210, S201:S202, S188:S189, S178:S179, S168:S169, S156, S155, S145:S146, S135:S136, S127, S125, S119:S120, S109:S110, S102:S103, S97, S95, S87:S88, S81, S79, S74, S73, S65:S66, S59:S60, S52:S53, S48, S46, S40:S41, S33, S27, S20	S7	=J7
T7, R10	T7	=S7
T8:T9, R285, R284, R283, R282, R280:R281, T281, R279, R254:R278, T274, T268, T261, R248:R251, R247, R225:R246, T241, T234, R219:R222, R217:R218, T218, R216, R196:R215, T211, T202, R188:R189, T189, R185:R187, R11:R184, T179, T169, T156, T146, T136, T120, T110, T103, T88, T74, T66, T60, T53, T41	T8	=T7+S8
Q10:Q14, Q285, Q284, Q283, Q282, Q280:Q281, Q279, Q266:Q277, Q255:Q264, Q248:Q251, Q247, Q240:Q245, Q233:Q238, Q226:Q231, Q219:Q222, Q217:Q218, Q216, Q207:Q214, Q198:Q205, Q195:Q196, Q188:Q189, Q185:Q187, Q174:Q183, Q163:Q172, Q161, Q150:Q158, Q138:Q148, Q127:Q136, Q116:Q125, Q106:Q114, Q97:Q104, Q90:Q95, Q85:Q88, Q81:Q82, Q70:Q79, Q59:Q68, Q48:Q57, Q37:Q46, Q26:Q35, Q16:Q24	Q10	=I9-I10
AF10:AF17	AF10	=-194.88322*(AE10/100)+100.06673
T20	T20	=T9+S20
U20, U285, U283, U280:U281, U273:U274, U268, U267, U260:U261, U255, U253, U247, U245, U240, U238, U233:U234, U231, U228, U224, U217:U218, U211, U210, U201:U202, U188:U189, U178:U179, U168:U169, U156, U155, U145:U146, U135:U136, U127, U125, U119:U120, U109:U110, U102:U103, U97, U95, U87:U88, U81, U79, U74, U73, U65:U66, U59:U60, U52:U53, U48, U46, U40:U41, U33, U27	U20	=R20-T20
T27, T95, T73, T40	T27	=T20+S27
X27, X40, X33	X27	=R27-$R$20
Y27, Y40, Y33	Y27	=T27-$T$20
Z27, Z288, Z285, Z283, Z280:Z281, Z273:Z274, Z268, Z260:Z261, Z255, Z253, Z247, Z245, Z240, Z228, Z224, Z217:Z218, Z211, Z201:Z202, Z188:Z189, Z178:Z179, Z168:Z169, Z156, Z145:Z146, Z135:Z136, Z127, Z125, Z119:Z120, Z109:Z110, Z102:Z103, Z97, Z95, Z87:Z88, Z81, Z74, Z65:Z66, Z59:Z60, Z52:Z53, Z48, Z46, Z40:Z41, Z33	Z27	=Y27/X27
T33, T280, T267, T253, T224, T217, R195, T109, T87, T59	T33	=T27+S33
V40, V65	V40	=(T40-27)/0.53+27
W40:W41, W285, W283, W280:W281, W273:W274, W268, W267, W260:W261, W255, W253, W247, W245, W240, W238, W233:W234, W231, W228, W224, W217:W218, W211, W210, W201:W202, W188:W189, W178:W179, W168:W169, W156, W155, W145:W146, W135:W136, W127, W125, W119:W120, W109:W110, W102:W103, W97, W95, W87:W88, W81, W79, W74, W73, W65:W66, W59:W60, W52:W53, W48, W46	W40	=R40-V40
V41, V119:V120, V109:V110, V102:V103, V97, V95, V87:V88, V81, V79, V74, V66, V59:V60, V52:V53, V48, V46	V41	=(T41-30)/0.6+30
X41, X74, X65:X66, X59:X60, X52:X53, X48, X46	X41	=R41-$R$40
Y41, Y74, Y65:Y66, Y59:Y60, Y52:Y53, Y48, Y46	Y41	=T41-$T$40
T46, T273, T260, T125, T102, T79, T65	T46	=T41+S46
T48, T285, T283, R253, T255, T247, R224, T240, T233, T127, T97, T81	T48	=T46+S48
T52, T245, T238, T228	T52	=T48+S52
V73, V285, V283, V280:V281, V273:V274, V268, V267, V260:V261, V255, V253, V247, V245, V240, V238, V233:V234, V231, V228, V224, V217:V218, V211, V210, V201:V202, V188:V189, V178:V179, V168:V169, V156, V155, V145:V146, V135:V136, V127, V125	V73	=(T73-30)/0.53+30
X81, X188:X189, X178:X179, X168:X169, X156, X145:X146, X135:X136, X127, X125, X119:X120, X109:X110, X102:X103, X97, X95, X87:X88	X81	=R81-$R$79
Y81, Y188:Y189, Y178:Y179, Y168:Y169, Y156, Y145:Y146, Y135:Y136, Y127, Y125, Y119:Y120, Y109:Y110, Y102:Y103, Y97, Y95, Y87:Y88	Y81	=T81-$T$79
Q83	Q83	=I82-I84
T119, T188, T178, T155, T145	T119	=T110+S119
T135, T210	T135	=T127+S135
T168, T201	T168	=T156+S168
X201	X201	=R201-$R$189
Y201	Y201	=T201-$T$189
X202	X202	=R202-$R$201
Y202	Y202	=T202-$T$201
X211, X228, X224, X217:X218	X211	=R211-$R$210
Y211, Y228, Y224, Y217:Y218	Y211	=T211-$T$210
Q224, Q253	Q224	=I222-I224
T231	T231	=T228+S231
X240, X255, X253, X247, X245	X240	=R240-$R$238
Y240, Y255, Y253, Y247, Y245	Y240	=T240-$T$238
X260	X260	=R260-$R$255
Y260	Y260	=T260-$T$255
X261	X261	=R261-$R$260
Y261	Y261	=T261-$T$260
X268	X268	=R268-$R$267
Y268	Y268	=T268-$T$267
X273	X273	=R273-$R$268
Y273	Y273	=T273-$T$268
X274, X280	X274	=R274-$R$273
Y274, Y280	Y274	=T274-$T$273
X281	X281	=R281-$R$280
Y281	Y281	=T281-$T$280
X283	X283	=R283-$R$281
Y283	Y283	=T283-$T$281
X285	X285	=$R$285-$R$283
Y285	Y285	=$T$285-$T$281
X288	X288	=AC3+AA288
Y288	Y288	=T4
AA288	AA288	=R285-U3
U289	U289	=[Reaction Calculation version1.8.xlsm]Reaction with Event (3)'!$E$25
V291	V291	=AC3+V290
AA291	AA291	=Z288
U291	U291	=U289*R2
U292	U292	=U289-U291
W292	W292	=U289-W290
V293	V293	=V290+T284
Y292	Y292	=R285*R2
Y293	Y293	=Y292*R3
W294	W294	=U289-V293
U295	U295	=V293/U289
U296	U296	=1-U295

 

[Defski]

I am not sure if this is what you were expecting?