Comditional Formatting Relative Recording VBA [Solved/Closed]

Report
Posts
3
Registration date
Thursday November 25, 2010
Status
Member
Last seen
November 26, 2010
-
Posts
3
Registration date
Thursday November 25, 2010
Status
Member
Last seen
November 26, 2010
-
Hello,

I want to compare a cell to its adjacent cell on the left. If the value of the selected cell is higher I'd like to colour it green, if it's the same I'd like to colour it orange and if it's below I'd like to colour it red. I can set up a macros that will do this if the cells I'm comparing are all in the same column, but I'd like one that would work anywhere on the page. Does anyone know how to do this?

Thanks


2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try this

do the conditional formatting (using "the formula is") on B2 (row 1 is header row) and then copy this for ALL THE CELLS---use pastespecial-format.
Posts
3
Registration date
Thursday November 25, 2010
Status
Member
Last seen
November 26, 2010

Unfortunately there's already formulae in the cells, so if I do that it overwrites the values I want compared.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
sorry for misunderstanding. I do not mean that you should change the formula in the cell. But you introduce the formula in "conditional formatting".

select the cell B2
click format-conditional formatting
in this window
under condition 1 choose "the formula is"
on the right side write the formula
e.g.
=B2>A2
click format in the second line of this window
format window opens
in this window choose "pattern" the right end of the choices at the top
choose color (green)
click ok
you are back in conditional formatting window
click add (at the bottom left)
in condition2 give the second formula and follow other steps (including click ok)
you are again back to conditional formatting windowa
again click add and type the third formula
repeat steps(including ok)
you are back in the conditional formatting window
now click ok in this window

check whether the original formula in B2 changed. It won't be.

you can have three conditions in conditional formatting in excel versions 2003 and before.
in excel 2007 you can have more.

now select B2 and click edit-copy (or control+c) now select all other cells from column 2 to the right and click edit-pastespecial and then choose format. click ok

the conditional formatting in the cells are created by there should not be any change in the existing formulas. you can test it by selecting any cell and click foramt -conditional formatting.

experiment in a small data.
Posts
3
Registration date
Thursday November 25, 2010
Status
Member
Last seen
November 26, 2010

Ah, that's fantastic, thanks for your help!