conditional formatting

[FROGGER24]

Hello,
if the delta between cells H2 and J2 is >=2% color cells red.

thanks in advance

 

[Special-K99]

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.

 

[FROGGER24]

Is the value in J2 <=2% of the value in H2... if yes color A2-J2 green, if no then red

 

[Special-K99]

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]

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>

 

[FROGGER24]

Peter, your formula worked perfectly... thank you again.

 

[FROGGER24]

Special K, I could not get your formulas to work in my situation. Thank you for the effort though.

 

[Peter_SSs]

Peter, your formula worked perfectly... thank you again.

You're welcome. Thanks for the follow-up.

Special K, I could not get your formulas to work in my situation.

That's really because you explanation in post 4 was not mathematically correct.