Advanced spreadsheet auto-formatting

Question

Is it possible to autoformat spreadsheet like this

IF A1 = C1 then A1:E1 cells background becomes light gray

I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet

Answer 1

In OOo as well as in Excel, just combine an absolute reference for the column with a relative reference for the row. This way, you can copy the conditional format to every cell, and it will evaluate the values of cells A and C in the current row.

In OOo, you will have to define the conditional formatting as follows:

1. Select a cell in row 1 that should be conditionally formatted, then select "Conditional Formatting..." from the Menu "Format";
2. Define the conditional format using the formula $A1=$C1. This is valid if you've selected a cell in row 1. If you're defining the format for a cell in row 2, use $A2=$C2. The $ defines an absolute reference that won't change if you copy the format to other cells. If the $ is missing, the reference is relative (it will change on pasting it to another cell). So, if you copy the reference $A1 from the first to the second row, it will point to $A2.
3. Copy the cell, select the other cells that should be conditionally formatted and select Menu "Edit" -> "Paste special" or CTRL+SHIFT+V;
4. In the "Paste Special" dialogue, in the "Selection" options, deselect everything except "Formats".

As result, the conditional formatting is defined for every cell selected in step 3, whith the formula pointing to cells A and C of the current row.

Answer 2

You should be able to do that with conditional formatting in excel, I'm not sure about the others. The exact method will depend on the version of excel.

EDIT: This should be what you need I don't have 2007 to test it though

In Excel 2007: Select range

On the Ribbon, go to the Home tab and click Conditional Formatting

Click New Rule

Click Use a Formula to Determine Which Cells to Format

For the formula, enter =A1=A3

Click the Format button.

Select a font colour to match the cell colour.

Click OK, click OK