0

I'm trying to set up a formula or rule (don't know which is the best to do this) on excel 2011. So for cell A1, I want to type in a value. I want cell B1 to show it in green if the value is more than A1 or red if it's less. 1) I also want to copy this formula or rule down column B so that it will change according to the value in column A (A1:B1, A2:B2, A3:B3, etc). If possible, I also want to copy this formula or rule to columns C, D & E so that if the value in cell C1 is greater than the value in B1, it will turn green and red if it's less, etc. Thank you in advance.

user3654187
  • 1
  • What have you tried so far? You should be able to set up a conditional format in `B1` then copy it down and across. – lurker May 19 '14 at 20:55
  • Hi Lurker, I tried using a conditional format on one cell but when I try to copy and paste it to a column of other cells, it keeps using the value from say cell A1 for all the cells in my B column. I want it to use the value from A1 for B1, A2 for B2, A3 for A3. Thanks – user3654187 May 19 '14 at 22:33
  • The conditional format formula must not have any $ in it - Excel loves to stick absolute references into those formulas. What is the actual formula you are using in the conditional format? – Jerry Jeremiah May 19 '14 at 23:16
  • Hm.. I wanted to use number with 2 decimals (1.00 to 100.00). So if B1 was greater than A1, I wanted B1 to be green and if less than red. Oh, I think the conditional format formula automatically was showing the $ symbol when I clicked on the other cell. Is there a way to get around this? Thanks – user3654187 May 19 '14 at 23:56
  • Select cell `B1` only and do conditional formatting on it. You can set on rule that says if `B1 > A1` make it green background, and another that says if `B1 < A1` make it red background. Be sure that you don't have dollar signs on the `B`, `A` or `1`. Once you save that, do a copy/paste format onto the rest of the cells in column `B`. You can also copy/paste format from `B1` to the cells in column `C`, etc. – lurker May 20 '14 at 00:15
  • Also [check this out](http://stackoverflow.com/questions/23295082/copying-conditional-formatting-in-excel/23307005#23307005) as reference on a better way of copying or rather mass applying conditional formatting. – L42 May 20 '14 at 03:17

1 Answers1

0

What you need is Conditional formatting. Steps to perform:

  1. Click a cell
  2. Go to menu: Format -> Conditional Formatting,
  3. Choose formula based formatting.
  4. Edit based on your formula

You can use the copy formatting icon to copy this conditional formatting to the whole column

David
  • 1
  • Thank you David. I tried doing that, but it keeps using the value from say cell A1 for all the cells in my B column. I want it to use the value from A1 for B1, A2 for B2, A3 for A3. Thanks. – user3654187 May 19 '14 at 21:29