How to conditionally format based on neighbour cell text?

Question

I'm trying to use conditional formatting on a table like this:

 ╔════════╦════════╗
 ║ Editor ║  Type  ║
 ╠════════╬════════╣
 ║ john   ║ Fun    ║
 ║ doe    ║ Travel ║
 ║ mark   ║ Movies ║
 ╚════════╩════════╝

where I need to color both columns according to Editor rows content. For example, if editor is john both Editor and Type need to be green, if doe red, etc., etc.

Of course in Editor column using simple "text is" conditional works flawlessly but in Type I tried indirect, address and several other formulas in the "Custom formula is" field.

What do I need to do?

Additionally, the columns repeat horizontally, like this:

╔════════╦════════╦════════╦════════╗
║ Editor ║  Type  ║ Editor ║  Type  ║
╠════════╬════════╬════════╬════════╣
║ john   ║ Fun    ║ john   ║ Fun    ║
║ doe    ║ Travel ║ doe    ║ Travel ║
║ mark   ║ Movies ║ mark   ║ Movies ║
╚════════╩════════╩════════╩════════╝

So I need a solution compatible with this.

Any ideas?

pnuts pnuts · Accepted Answer · 2015-11-21T11:31:43

Select ColumnsA:D and Format - Conditional formatting..., Custom formula is and:

 =$A1="john"

Choose green for Formatting style and Done.

Repeat with a new rule for each separate colour, so for red:

=$A1="doe"

If required, adjust A1 and Apply to range to suit.

BTW Strictly speaking (despite much precedent) your Q is probably off topic here (though not necessarily at Web Applications).

How to conditionally format based on neighbour cell text?

1 Answers