I HAVE A QUESTION ON CONDITIONAL FORMATTING.

[SALSINHO]

I want cell A2 to highlight "Green" if its value increases more than 58% of the value of cell A1.


and


I want cell A2 to highlight "Red" if its value decreases more than 58% of the value of cell A1.


How can I do the above simultaneously on Excel 2010 conditional formatting? Your help will be much appreciated.


Thanks,


Salsinho

salsinho@gmail.com

 

[Hui]

Salsinho

You will use a Conditional format based on a formula and it will; have 2 rules

Rule 1 =B1>0.58*A1 and set format as Green

Rule 2 =B1<=0.58*A1 and set format as Red


When you say increases more than 58% of A1 do you mean 58% of A1 or a 58% increases above the value of A1

If it is 58% of the values in A1 the former formula are ok


If it is an increase above 58% above the value in A1 change the formulas to those below

Rule 1 =B1>1.58*A1 and set format as Green

Rule 2 =B1<=1.58*A1 and set format as Red

 

[keymaster]

Here is a simple intro to CF


http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/


and a video to help you get started


http://chandoo.org/wp/excel-tutorial/using-conditional-formatting/

 

[SALSINHO]

Hi, Chandoo.. Thanks a lot for your help. But when I try to copy the formula to the same column range below it, it does not work out. For example, I have input the following conditional formatting in cell A2:


=A2>0.58*A1 and set format as Green

=A2<=0.58*A1 and set format as Red


But when I copy this formatting from cell A2 and paste it to the range A3:A100, it does not come properly. All what I copied & pasted was relative references (don't have any $ signs). But for example when I check the formatting in cell A58, it reads as follows:


=A2>0.58*A1 and set format as Green

=A2<=0.58*A1 and set format as Red


But it should read as follows:


=A58>0.58*A57 and set format as Green

=A58<=0.58*A57 and set format as Red


But It is not coming right. What could I be doing wrong? Once again, I assure you that I have not put any $ signs.


Please try and help.


Thanks,


Salsinho.

 

[keymaster]

First clear the conditional formatting by selecting A1:A300 and remove CF.


1. Now, select A2:A300 and go to CF

2. New rule

3. A2>0.58*A1 - set format to green

4. A2<=0.58*a1 - set format to red


This should work.

 

[SALSINHO]

Chandoo.. Thanks a lot. With what you suggested, and with a little tinkering with the formulas, I was able to get what I want. Many thanks for your help..