CF formula help.

[serge]

Hi everyone,

I need a CF formula to highlight the number in the previous column, and if the new number to highlight is greater than the previous one it should be in red and if not it should be in green.

my starting column is in A1:A20.
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20.

B1:B2.
10,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20.
So the number 10 in B1 is the number to find in A1:A20 and should be in red because is higher than 1 in A1.
Then if I have 5 in C1 int should highlight the number 5 in green in column B1:B20. and if 18 in D1 then the number 18 in C1:C20 should be in red, Etc...

<table width="104" border="0" cellpadding="0" cellspacing="0"><col style="width: 20pt;" width="26" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 20pt;" width="26" height="17">1</td> <td class="xl66" style="width: 20pt;" width="26">10</td> <td class="xl66" style="width: 20pt;" width="26">5</td> <td class="xl65" style="width: 20pt;" width="26">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">2</td> <td class="xl67">1</td> <td class="xl67">10</td> <td class="xl67">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">3</td> <td class="xl67">2</td> <td class="xl67">1</td> <td class="xl67">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">4</td> <td class="xl67">3</td> <td class="xl67">2</td> <td class="xl67">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">5</td> <td class="xl67">4</td> <td class="xl67">3</td> <td class="xl67">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">6</td> <td class="xl67">5</td> <td class="xl67">4</td> <td class="xl67">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">7</td> <td class="xl67">6</td> <td class="xl67">6</td> <td class="xl67">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">8</td> <td class="xl67">7</td> <td class="xl67">7</td> <td class="xl67">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">9</td> <td class="xl67">8</td> <td class="xl67">8</td> <td class="xl67">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" height="17">10</td> <td class="xl67">9</td> <td class="xl67">9</td> <td class="xl67">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">11</td> <td class="xl67">11</td> <td class="xl67">11</td> <td class="xl67">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">12</td> <td class="xl67">12</td> <td class="xl67">12</td> <td class="xl67">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">13</td> <td class="xl67">13</td> <td class="xl67">13</td> <td class="xl67">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">14</td> <td class="xl67">14</td> <td class="xl67">14</td> <td class="xl67">13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">15</td> <td class="xl67">15</td> <td class="xl67">15</td> <td class="xl67">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">16</td> <td class="xl67">16</td> <td class="xl67">16</td> <td class="xl67">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">17</td> <td class="xl67">17</td> <td class="xl67">17</td> <td class="xl67">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">18</td> <td class="xl67">18</td> <td class="xl67">18</td> <td class="xl67">17</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">19</td> <td class="xl67">19</td> <td class="xl67">19</td> <td class="xl67">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">20</td> <td class="xl67">20</td> <td class="xl67">20</td> <td class="xl67">20</td> </tr> </tbody></table>
Thank you all for any help. Serge.

 

[barry houdini]

Hello serge,

Are those the only numbers you expect to be formatted in that example? In column C 1,2,3 and 4 are also lower than the corresponding value in column B so shouldn't those 4 numbers be formatted green in column B?

 

[serge]

Hi barry,

Sorry I stepped out for a while, to answer your question its NO.

There is ONLY ONE NUMBER per each column that must be formatted in either Red or green depending on the number before.

My data don't stop at 20 like the example but goes up to 60 rows, and over 600 columns.

I just need all the numbers from row 1 to be formatted in red if it is greater than the previous number or in green if it's lower than the previous number.

More example below :

<table width="260" border="0" cellpadding="0" cellspacing="0"><col style="width: 20pt;" width="26" span="5"> <col style="width: 20pt;" width="26" span="5"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt; width: 20pt;" width="26" align="center" height="17">1</td> <td class="xl64" style="width: 20pt;" width="26" align="center">10</td> <td class="xl64" style="width: 20pt;" width="26" align="center">5</td> <td class="xl63" style="width: 20pt;" width="26" align="center">18</td> <td class="xl63" style="width: 20pt;" width="26" align="center">15</td> <td class="xl63" style="width: 20pt;" width="26" align="center">12</td> <td class="xl63" style="width: 20pt;" width="26" align="center">9</td> <td class="xl63" style="width: 20pt;" width="26" align="center">6</td> <td class="xl63" style="width: 20pt;" width="26" align="center">17</td> <td class="xl63" style="width: 20pt;" width="26" align="center">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">17</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">5</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">6</td> <td class="xl67" align="center">5</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">7</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> <td class="xl65" align="center">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">9</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="center" height="17">10</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">11</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">12</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">13</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> <td class="xl65" align="center">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">14</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">15</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">15</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">13</td> <td class="xl65" align="center">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">18</td> <td class="xl65" align="center">18</td> <td class="xl67" align="center">18</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">17</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">19</td> <td class="xl65" align="center">16</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl64" style="height: 12.75pt;" align="center" height="17">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">20</td> </tr> </tbody></table>
Thank you in advance for all your help. Serge.

 

[barry houdini]

Select the whole range of data and then use this formula for the red condition

=(A1=B$1)*(B$1>A$1)

and for green condition same formula but with < in place of >

Ensure that the $ signs are as per my formula, i.e. the first A1 doesn't have one.......

 

[serge]

Hi Barry,

Thank you for answering my post, but the formula format the number right after the one that is suppose to be ?? one number to far, otherwise the red and green are good.

Thanks. Serge.

 

[barry houdini]

My formula assumes that your table starts at A1. If it starts elsewhere then you need to adjust the formula..........

 

[serge]

Hi Barry,

I want to apologize to you.

I didn't start the CF in A1 but in A2, that is why it was off by one number, my bad.

Thanks a lot for the help. Serge.