[Solved] Rel. cond. formatting across a large grid of cells

[chris2600]

I am trying to apply conditional formatting to cells that already contain information: i.e. If a1 = 1 and aa1 does not = 1, change the background color of both cells. I need to do this for a large number of cells [a1 through z80 ["grid 1"] being compared to aa1 through az80 ("grid 2")] after the data in both grids has already been entered.
My questions are:
1) Is there a function such as "copy right" that will copy this kind of conditional formatting instead of copying the cell contents (so I don't have to do each cell individually) in a way that will identify each reference cell relatively (such as "26 cells to the left of this cell")?
2) Can this be done without over-writing data already entered into the cells?

[FJCC]

Here is how I would do it. First, select all of the cells in grid one and notice which cell is active. Let's say you start in the lower right corner (Z80) and select up to A1 so that A1 is active. Go to the menu Format -> Conditional Formatting. Set the beginning of Condition 1 to Formula is, enter the formula A1<> AA1 and set the Cell Style to whatever you want to see when the cells are not equal. Click OK and all of the selected cells will have conditional formatting that looks 26 cells to the right to see if the values are equal. You can then repeat this in grid 2. The formula will look the same but when written in cell AA1 it means "look 26 cells to the left".

[chris2600]

Thanks -- works like a charm!

[ken johnson]

FJCC's method is the way I too would do it, however, strict interpretation of...

If a1 = 1 and aa1 does not = 1

leads to this formula in Formula is...

Code: Select all

AND(A1=1;AA1<>1)

I could be wrong though and you might really be wanting to test the equality of corresponding values in the two grids as FJCC has assumed.

Ken Johnson

[chris2600]

Ah - thanks for the clarification. I am wanting to test the equality of the values, but your comment is well taken!