Дмитрий Эгрэгор

Надо сделать два условных форматирования - для красного и зелёного соответственно.
Как пример, для столбца A:A: слово справа = "красный"
Условное форматирование - Своя формула
=B1="красный"
А так, уже всё придумано и используется. При копировании ячеек и/или формата условное форматирование тоже копируется, и никакой проблемы нет.

В таком случае проще всего завести справочник на отдельном листе, и там искать соответствие.
А если хочется без отдельного справочника, можно так: через ВПР(запрос; диапазон; индекс; [отсортировано]) и литерал массива, вот так:

=ВПР(E9;{
  "Андрей"\"1й";
  "Антон"\"2й";
  "Максим"\"3й"
};2;0)