Тут, наверное, можно сделать 2-мя путями
1) перебором каждой ячейки в ряду
2) использовать функцию рабочего листа МАКС() и поиск этого максимального значения

Вот вариант второго пути

да, круто)
но в русском офисе формула MAX(R) не работает, надо МАКС(R)
А при повторном запуске макроса, он добавляет новое (2 и 3) идентичное условие, а при 4-ом запуске происходит ошибка 1004 (в офисе 2003, т.к. там можно только 3 условия)
Поэтому, с позволения KL , дополню

Цитата из Справки Excel по FormatCondition.Add

expression.Add(Type, Operator, Formula1, Formula2)
expression - Required. An expression that returns a FormatConditions object.

Type Required XlFormatConditionType . Specifies whether the conditional format is based on a cell value or an expression.
XlFormatConditionType can be one of these XlFormatConditionType constants.
- xlCellValue The conditional format is based on a cell value.
- xlExpression The conditional format is based on an expression.

Operator Optional Variant. The conditional format operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween , or xlNotEqual . If Type is xlExpression, the Operator argument is ignored.

Formula1 Optional Variant. The value or expression associated with the conditional format. Can be a constant value, a string value, a cell reference, or a formula.

Formula2 Optional Variant. The value or expression associated with the second part of the conditional format when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored). Can be a constant value, a string value, a cell reference, or a formula.

Это, наверное, из-за того, что "между" работает только когда тип "Значение" (1), а не "Формула" (2) .
Цитатат из Справки: "If Type is xlExpression , the Operator argument is ignored ."

Попробуйте так