Excel нельзя использовать относительные ссылки для цветовых шкал

 Сразу спойлер - данное решение не является полным аналогом цветовых шкал, т.к. не может подсвечивать промежуточными значениями цвета.

По сути дела сама фича (цветовая шкала Excel) работает по принципу - значение ячейки (не важно сколько их в диапазоне, работает над каждой) в пределах диапазона соответствует рассчитываемому значению самим форматированием, а значение для диапазона считается  как МАКС(Выделенного диапазона):

В связи с такой логикой, получить выделение строки по сумме этой строки с относительными ссылками невозможно.

А задачу надо решать, а как?

Первый вариант: использовать суммирование с относительными ссылками в обычном условном форматировании связывая полученные значения с числом, а к числу привязываем цвет.
Допустим так значения: до 1000 включительно =1, от 1001* до 2000 включительно = 2, до 3000 = 3
Допустим так цвета: 1 - зеленый, 2 - желтый, 3 - красный

Диапазон у нас один = $B$8:$D$10 для всех трех правил, а формула будет отличаться только одной цифрой:

#зеленый
=ЕСЛИ(СУММ($B8:$D8)<=1000;1;ЕСЛИ(И(СУММ($B8:$D8)>=1001;СУММ($B8:$D8)<=2000);2;ЕСЛИ(СУММ($B8:$D8)<=3000;3;0)))=1
#желтый
=ЕСЛИ(СУММ($B8:$D8)<=1000;1;ЕСЛИ(И(СУММ($B8:$D8)>=1001;СУММ($B8:$D8)<=2000);2;ЕСЛИ(СУММ($B8:$D8)<=3000;3;0)))=2
#красный
=ЕСЛИ(СУММ($B8:$D8)<=1000;1;ЕСЛИ(И(СУММ($B8:$D8)>=1001;СУММ($B8:$D8)<=2000);2;ЕСЛИ(СУММ($B8:$D8)<=3000;3;0)))=3

Поясню немножко, в формуле указан диапазон суммирования $B8:$D8, при этом диапазон форматирования =$B8:$D12, как же это работает?, все дело в знаке $, который уточняет диапазон, в нашем случае он будет суммировать каждую строку. При использовании знака $ нужно учитывать изначальный диапазон суммирования:

И второй момент, условия выполняются друг за другом, как только условие выполнится, работа формулы прекращается, поэтому в последнем условии мы не проверяем является ли значение одновременно и большим 2001 и меньшим 3000, т.к. дойти до этого условия может только число явно большее 2000. Вот тут еще одна остановка(*) - в первом условии сказано равно или меньше 1000, значит 1001 уже попадает во второе условие, поэтому можно писать просто <=1000, >=1000 ИЛИ <=2000, <3000

Второй вариант: Чуть более универсальный, в нем мы предположим что у нас есть некое максимальное число в ячейке F14, выше которого мы не будем учитывать, а все что меньше поделим на трети и применим нашу цветовую схему

#зеленый
=ЕСЛИ(СУММ($B16:$D16)<=$F$14/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=$F$14/3;СУММ($B16:$D16)<=$F$14/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=$F$14;3;0)))=1
#желтый
=ЕСЛИ(СУММ($B16:$D16)<=$F$14/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=$F$14/3;СУММ($B16:$D16)<=$F$14/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=$F$14;3;0)))=2
#красный
=ЕСЛИ(СУММ($B16:$D16)<=$F$14/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=$F$14/3;СУММ($B16:$D16)<=$F$14/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=$F$14;3;0)))=3

Третий вариант: избавимся от F14 и будем оперировать всем диапазоном, как это делает цветовая шкала. Вместо F14 укажем Сумм(диапазон форматирования)

#зеленый
=ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=СУММ($B$20:$D$22)/3;СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22);3;0)))=1
#желтый
=ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=СУММ($B$20:$D$22)/3;СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22);3;0)))=2
#красный
=ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3;1;ЕСЛИ(И(СУММ($B16:$D16)>=СУММ($B$20:$D$22)/3;СУММ($B16:$D16)<=СУММ($B$20:$D$22)/3*2);2;ЕСЛИ(СУММ($B16:$D16)<=СУММ($B$20:$D$22);3;0)))=3

Вот для такой формулы покраска изменится:

Четвертый вариант: Можно рассмотреть еще вариант с динамическим именованным диапазоном, т.е. диапазон определения МАКС и диапазон форматирования равны именованному диапазону. Для этого создадим диапазон формулой:

=СМЕЩ($B$2;;;СЧЁТЗ($B:$B);СЧЁТЗ($2:$2))

Не люблю использовать первый столбец или строку, поэтому левый верхний угол = B1, а назовем ТаблицаФорматирования

Тогда формула определения общего числа будет:

=Сумм(ТаблицаФорматирования)

А формула условного форматирования вообще монструозной:

#зеленый
=ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3;1;ЕСЛИ(И(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))>=СУММ(ТаблицаФорматирования)/3;СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3*2);2;ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования);3;0)))=1
#желтый
=ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3;1;ЕСЛИ(И(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))>=СУММ(ТаблицаФорматирования)/3;СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3*2);2;ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования);3;0)))=2
#красный
=ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3;1;ЕСЛИ(И(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))>=СУММ(ТаблицаФорматирования)/3;СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования)/3*2);2;ЕСЛИ(СУММ(СМЕЩ($B2;;;1;СЧЁТЗ(2:2)))<=СУММ(ТаблицаФорматирования);3;0)))=3

Эта формула в общей формуле определяет диапазон строки

=СМЕЩ($B2;;;1;СЧЁТЗ(2:2))

К сожалению диапазон форматирования не понимает динамики, я даже попробовал использовать ДВССЫЛ:

=ДВССЫЛ("R2C2:"&"R"&СЧЁТЗ(B:B)+1&"C"&СЧЁТЗ(2:2)+1;ЛОЖЬ)

для определения форматируемого диапазона, но увы, либо макрос, либо обновлять постоянно диапазон, или определить диапазон сразу максимально возможным.

НО, с динамическим диапазоном нужно быть осторожным, т.к. диапазон, определенный при помощи СЧЁТЗ(), не понимает пустые столбцы, т.о. может получится так:

Пятый вариант: макросы, но здесь не о них. Пример покраски мы видели 7 лет назад здесь