Conditional Formatting help, highlighting a range between 2 Values?

Defski

New Member
Joined
Apr 25, 2018
Messages
16
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,593
Office Version
  1. 365
Platform
  1. Windows
How about
=and($R7>=$U$3,$R7<=$U$5)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,593
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2018
Messages
16

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,593
Office Version
  1. 365
Platform
  1. Windows
Make sure that the new rule is at the top of the list & it should overrule all the other rules.
 

Defski

New Member
Joined
Apr 25, 2018
Messages
16

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,593
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2018
Messages
16
Book1
S
40.604
wk42HS
Cell Formulas
RangeFormula
B1B1=WEEKNUM(H2)
AA2AA2=(1-$R$1)*$V$3+($R$1*(S4*$V$3))*(1-$R$2)
AB2AB2=(1-$R$1)*$V$3+($R$1*(R4*$V$3))*(1-$R$2)
V3V3=U5
W3W3=AB2+$AB$4+(-AB2*$R$2)
X3X3=AA2+$AB$4+(-AA2*$R$2)
V4V4=VLOOKUP(V3+0.2,R11:R1769,1,TRUE)
W4:X4W4=VLOOKUP(W3+1,$T$11:$T$1769,1,TRUE)
AB3AB3=T3-AB4
AC3AC3=U4+AC4
AB4AB4=\[Reaction Calculation version1.8.xlsm]Reaction with Event (3)'!$J$28
AC4AC4=(U4-AB4)*(1*R2)
B4B4=RIGHT(YEAR(H2),1)&CHAR(MONTH(H2)+64)&E5&DAY(H2)&"AlN"&B1
R4R4=((1-$R$3)*(1-$R$1)+$R$3)
S4S4=((1-$S$3)*(1-$R$1)+$S$3)
T4T4=VLOOKUP(T3+0.1,T11:T1769,1,TRUE)
E5E5=IF(DAY(H2)<10,"0","")
U3U3=VLOOKUP(AC3,R10:R1258,1,TRUE)
U4U4=$AB$3/($R$4/1)
U5U5=$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, S20S7=J7
T7, R10T7=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, T41T8=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:Q24Q10=I9-I10
AF10:AF17AF10=-194.88322*(AE10/100)+100.06673
T20T20=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, U27U20=R20-T20
T27, T95, T73, T40T27=T20+S27
X27, X40, X33X27=R27-$R$20
Y27, Y40, Y33Y27=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, Z33Z27=Y27/X27
T33, T280, T267, T253, T224, T217, R195, T109, T87, T59T33=T27+S33
V40, V65V40=(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, W46W40=R40-V40
V41, V119:V120, V109:V110, V102:V103, V97, V95, V87:V88, V81, V79, V74, V66, V59:V60, V52:V53, V48, V46V41=(T41-30)/0.6+30
X41, X74, X65:X66, X59:X60, X52:X53, X48, X46X41=R41-$R$40
Y41, Y74, Y65:Y66, Y59:Y60, Y52:Y53, Y48, Y46Y41=T41-$T$40
T46, T273, T260, T125, T102, T79, T65T46=T41+S46
T48, T285, T283, R253, T255, T247, R224, T240, T233, T127, T97, T81T48=T46+S48
T52, T245, T238, T228T52=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, V125V73=(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:X88X81=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:Y88Y81=T81-$T$79
Q83Q83=I82-I84
T119, T188, T178, T155, T145T119=T110+S119
T135, T210T135=T127+S135
T168, T201T168=T156+S168
X201X201=R201-$R$189
Y201Y201=T201-$T$189
X202X202=R202-$R$201
Y202Y202=T202-$T$201
X211, X228, X224, X217:X218X211=R211-$R$210
Y211, Y228, Y224, Y217:Y218Y211=T211-$T$210
Q224, Q253Q224=I222-I224
T231T231=T228+S231
X240, X255, X253, X247, X245X240=R240-$R$238
Y240, Y255, Y253, Y247, Y245Y240=T240-$T$238
X260X260=R260-$R$255
Y260Y260=T260-$T$255
X261X261=R261-$R$260
Y261Y261=T261-$T$260
X268X268=R268-$R$267
Y268Y268=T268-$T$267
X273X273=R273-$R$268
Y273Y273=T273-$T$268
X274, X280X274=R274-$R$273
Y274, Y280Y274=T274-$T$273
X281X281=R281-$R$280
Y281Y281=T281-$T$280
X283X283=R283-$R$281
Y283Y283=T283-$T$281
X285X285=$R$285-$R$283
Y285Y285=$T$285-$T$281
X288X288=AC3+AA288
Y288Y288=T4
AA288AA288=R285-U3
U289U289=[Reaction Calculation version1.8.xlsm]Reaction with Event (3)'!$E$25
V291V291=AC3+V290
AA291AA291=Z288
U291U291=U289*R2
U292U292=U289-U291
W292W292=U289-W290
V293V293=V290+T284
Y292Y292=R285*R2
Y293Y293=Y292*R3
W294W294=U289-V293
U295U295=V293/U289
U296U296=1-U295
 

Forum statistics

Threads
1,147,499
Messages
5,741,507
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top