compare 2 cells and color green or red - Simple? nope

[mrnoonan]

I'm using Mac Excel 2011 (based on Win Excel 2010 I think). Been fighting Conditional Formatting for hours and web searched 'til blind. no joy.

Found answer: http://blogs.office.com/2012/02/27/c...es-simplified/

The Format Painter clobbers my cell Outlines and Bold values. [ applies ALL formats in selected cell to target array/cell. ]

When I save I get the warning that I will lose functionality when I save to . http://blogs.office.com/2012/02/27/c...es-simplified/. I'm using an .xls file to begin with. ??
After the save and reopen the simple 2 rules per row are now changed to an array in the "Assigned to" box. ??
I lose the cell rule reference on all conditional cells. ??
Saving as Excel 95 - 2004 workbook (.xls) as target users mostly have Windows Excel of some version of another. Dumbing down to the lowest common platform.

This workbook, for an association budget, has 5 worksheet tabs with some interaction between columns described below.

I have a 13 column x 118 row worksheet. 4 columns are of interest here and except for header, space line, and totals, the rows in vertical cells are identical in function.

Column H contains Trending data from another worksheet tab; just a simple equal reference to another budget sheet's same row.
Column I contains the current year budget as numerical data. no formulas or references..
The 2 conditionals for these cells are:
In<Hn make cell red background
In<Hn make cell green background (both with black numbers)
Column J Initially pulls the data from cell =In. At this point, office manager is looking at last year's budget as a starting point.
If he enters new data, the formula gets clobbered and one of the colors a shown.
The 2 similar conditionals for these cells are:
Jn<In make cell red background
Jn<In make cell green background (both with black numbers)
Column K Initially pulls the data from cell =Jn. At this point, Budget Committee is looking at the office Manger's budget as a starting point.
If the Committee updates any data in it's own column, the formula gets clobbered and one of the colors a shown.
The 2 similar conditionals for these cells are:
Kn<Jn make cell red background
Kn<Jn make cell green background (both with black numbers)

I was able to enter a pair of rules for each cell without an array being created. I did this by skipping cells on the rows say I4, J4, and K4. Then using cell I4 and the Painter, I dragged down at beginning at I5 across all three columns and down to the bottom. Checking random cells with manage Rules the single cell references held and the relative column/row values were changed to their respective cell references. I cleaned up the Painter sweep by removing conditionals from space lines and added back bold black lines and numbers. [did this cause the "Assigned to" arrays? I didn't check.]
I Saved As... per above, clicked Continue on the warning messages that I might lose formatting. On reopening the file, the arrays were there.

Later on, I've opened the file and the colors are not displayed. The conditional rules are but the rightmost part of every cell contains a REF#, for example I5<REF#.

Help! please.

I may be doing this wrong but with the arrays now set and randomly looking at a cell's rule, the cell rule points to the upper most cell I4 which is not the correct rule set for this specific cell, i.e., I in cell H65 and the rule shown says I4<H4 and I4>H4. Why?

Please advise. Sorry for the detail but HTH.

Mike Thx in advance.