conditional formatting

trimmer69

Active Member
Joined
May 22, 2004
Messages
402
Hello,
if the delta between cells H2 and J2 is >=2% color cells red.

thanks in advance
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
What do you mean the "delta" ?

"between cells H2 and J2 is >=2%"
The only cells between H2 and J2 is I2.
So I2 >= 2% ?
If this is not what you mean then you need to be more specific.

Provide some example data and clarify what the cell references are.
 
Last edited:

trimmer69

Active Member
Joined
May 22, 2004
Messages
402
Is the value in J2 <=2% of the value in H2... if yes color A2-J2 green, if no then red
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
Select A2:J2

Conditional Formatting
New Rule
Use a formula to determine...
2 Rules needed

=J2<=2%*H2
format as green

=J2>2%*H2
format as red
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,606
Office Version
365
Platform
Windows
Is the value in J2 <=2% of the value in H2... if yes color A2-J2 green, if no then red
Hmm, delta normally means the difference between 2 numbers. Based on that, my suggestion would be to select A2:Jxx and apply the two CF rules shown.

<b>CF Delta</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:23px;" /><col style="width:23px;" /><col style="width:23px;" /><col style="width:23px;" /><col style="width:23px;" /><col style="width:23px;" /><col style="width:23px;" /><col style="width:56px;" /><col style="width:23px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">100</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">99</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">98</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">100</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">100</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">52</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">1.2356</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">1.2357</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">24</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">25</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">23.5</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">23.9</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">88.01</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">100</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">23</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">text</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">text</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">48</td><td style="background-color:#ff0000; font-size:10pt; "> </td><td style="background-color:#ff0000; font-size:10pt; text-align:right; ">50</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">17</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">22</td><td style="background-color:#92d050; font-size:10pt; "> </td><td style="background-color:#92d050; font-size:10pt; text-align:right; ">22</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >A2</td><td >1. / Formula is =AND(COUNT($H2,$J2)=2,ABS($H2-$J2)/ABS(MIN($H2,$J2))<0.02)</td><td style="background-color:#92d050; ">Abc</td></tr><tr><td >A2</td><td >2. / Formula is =AND(COUNT($H2,$J2)=2,ABS($H2-$J2)/ABS(MIN($H2,$J2))>=0.02)</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table>
 

trimmer69

Active Member
Joined
May 22, 2004
Messages
402
Special K, I could not get your formulas to work in my situation. Thank you for the effort though.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,919
Messages
5,411,228
Members
403,351
Latest member
NTLH_Excel

This Week's Hot Topics

Top