PDA

View Full Version : Using conditional formatting



Tenspeed39355
10-01-2012, 01:43 PM
I cannot find the answer that I am looking for so I am back. I have some
stock symbols in column A. In column B I have the historical market prices of the symbols. In column C I have the current market prices. In column D I have the changes. How do I color each cell in column D so that when a cell
in column D changes the color changes. If the change is up I would like green. If the cell changes down I would like red. Each cell in column D has to be independ of the other cells in column D. Any help with this.
Thanks in advance.
Max

Bob Phillips
10-01-2012, 02:51 PM
Use CF with a formulae of

=$B2>$C2 - red
=$B2<$C2 - green

Tenspeed39355
10-01-2012, 06:03 PM
Hi This is not going to work. What I need is when the data in column C
changes from what is in the cell to a value more or less than the current
value the color changes to green or red depending which way the new value is. Example C2 is $10.00 and changes to $10.05. C2 color is now green.
Example C2 is $10.00 and changes to $9.98. The color goes to red.
This has to hold true for all the cells in column C. Each cell changes due to
the value change in each cell.
Max

Aussiebear
10-01-2012, 09:59 PM
Hi Max. I'm assumng you are using 2007,if not then you'll this won't assist.

1. Select cells from D2 to end of data in column D you wish the CF to apply to then, in the Home tab select Conditional Formatting, and Manage Rules.
2. Select the option Use a Formula to determine which cells to format, then enter the formula =$D2>$C2, and apply a green formatting of your choice.
Select OK & Ok.

At this point you are still in the Rules Manager dialog box.

3. Select New Rule, Use a formula to determine which cells to format and enter the rule =$D2<$C2, making sure it applies to the relevant range in column D, and format to red, Select OK, Ok and Apply.

Back on your worksheet you now have two CF rules applying to your range and if you change the values of the cells the colour will change accordingly.

Bob Phillips
10-02-2012, 12:21 AM
Hi This is not going to work. What I need is when the data in column C
changes from what is in the cell to a value more or less than the current
value the color changes to green or red depending which way the new value is. Example C2 is $10.00 and changes to $10.05. C2 color is now green.
Example C2 is $10.00 and changes to $9.98. The color goes to red.
This has to hold true for all the cells in column C. Each cell changes due to
the value change in each cell.
Max

Repeating what you said previously doesn't really add a lot.

Did you try it? As far as I can see, what I gave you fits that bill exactly.

Tenspeed39355
10-05-2012, 08:18 AM
I made an error when entering the formula. Thanks your work