Contitional formatting between two columns - one tests the other

Hopefully this is simple. I've used Excel for years but Conditional Formatting continues to elude me.

We'll be doing a budgeting meeting showing an Excel sheet from my PC projected on a big screen. When a value in a cell is changed one of two colors should appear - Red if higher, Green if lower (if the value is different from the adjacent column) or no change.

Say the columns are A and B. A is the numeric reference column and B is the column to be modified.

Initially both columns are equal to each other; column A has a number value; Column B has "=Ax where x is the same row number as A.

When a number is entered in say B23 the "=A23" is overwritten with a new number - could be higher, lower or the same as in A23. The conditional format in B23 is triggered.

I've tried the new formatting in Excel 2011 (Mac) where the conditional range is first selected. I understand that the first cell's (B1) conditionals will be used and adjusted for each row in the range, e.g. B23<A23. $ cannot be used in the row specification. [I was thrown off for awhile when I looked at the conditionals in another row in B and saw that the formulas referenced B1.]

I've tried "Cell Contains" and "Use a Formula to determine which cells to format" without success.

I have notice that when Cell Contains Equal To is entered as b1, the B becomes capitalized as B1 but not for the b1<a1 or b1>a1 formulas. don't know why. The formula is in error?

All numbers are using a Custom formatting for dollar signs, e.g., $ 74.0240 (whole numbers) but I suspect that formatting has no influence on conditional formatting, only the values are used.

see attached snip from my sheet. note hash marks for rows that are different.

TIA, Mike


Workbook2.xlsx

hi mrnoonan, welcome to the forum. i don't have a macbook, so try to find something similar out of this.
1. you don't have to do a Conditional formatting if they are equals to each other. by default, there will be no colour
2. your formula shouldn't include the double quotes unless you are doing text comparison.
3. i don't know what "l3>k3" is supposed to be. i'm guessing your actual data is Column L vs Column K? if so, follow these steps (modify when necessary to work with Mac)
- select the range you want to apply to (say from B3:B18)
- go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=B3>A3
Format Red

Repeat the steps for green but with this formula
=B3<A3