1

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet.

To facilitate this, I want to use conditional formatting to change the color of a cell if its value matches the value of the cell in the same position on the default sheet.

I have this formula in the Conditional Formatting 'custom formula is' field:

=match(C4,indirect("Default!C4"),0)

This works well for cell C4, but I want to apply this same conditional formatting to all the cells in a range, and I can't find any way to accomplish this short of manually changing the referenced cell for each and every cell in the document.

Any help?

  • Ideally, yes. It should be something I can apply across a range. – Seanonymous Dec 2 '14 at 0:21
1

This does the trick:

=IF(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)=INDIRECT("Default!R"&ROW()&"C"&COLUMN(),FALSE),TRUE,FALSE)

It's not exactly concise, but it works.

  • And if you only need the formatting to be applied if the cells are not empty: =AND(IF(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)=INDIRECT("Default!R"&ROW()&"C"&COLUMN(),FALSE),TRUE,FALSE),NOT(ISBLANK(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)))) – Seanonymous Dec 2 '14 at 0:38
0

Promoting pnuts' comment to an answer: the custom formula

=A1<>indirect("Another Sheet!R"&row()&"C"&column(), 0)

will apply formatting to all cells in a range (such as A1:Z, etc) that you provide in the conditional formatting dialog.

Explanation: "0" indicates notation in R1C1 format (available only in new Google Sheets), the concatenated string computes such notation for a cell to which we compare. This comparison command is pretty slow, so you may want to restrict the range to a part of worksheet that's of current interest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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