up vote 1 down vote favorite
share [g+] share [fb]

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

link|improve this question
77% accept rate
feedback

2 Answers

up vote 2 down vote accepted

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. conditional formatting
  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". Paste Special

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.

link|improve this answer
feedback
up vote 2 down vote

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

link|improve this answer
I have excel 07 – Templar Aug 19 '11 at 21:24
Made mistake in my question it should be if A1=C1, but anyway this way it works only for 1 row, I want that it would work for entire sheet, like if A1=C1 then change colour A1:E1, if A2=C2 then change colour A2:E2 and etc. – Templar Aug 20 '11 at 8:46
I'm sure that can be done but I'm on a PC without office so I can't test it. It might be worth doing the first row then using the format pasting option to apply it to the other rows, I think that will sort the cell references out. – Col Aug 20 '11 at 9:01
ok, on excel its pretty same like tohuwawohu answered about OO, but anyway thanks for help – Templar Aug 20 '11 at 10:41
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.