Условное форматирование Excel: используйте цвет заливки на основе существующего цвета заливки другой ячейки

Question

В следующей таблице представлено значение, с которым я работаю в Microsoft Excel.

+---+----+-----+---------+-----+
|   | A  |  B  |    C    |  D  |
+---+----+-----+---------+-----+
| 1 |    | Min | Current | Max |
| 2 | LF | 8   | 13      | 20  |
+---+----+-----+---------+-----+

Мне бы хотелось, чтобы значение, расположенное в текущем столбце, меняло цвет в зависимости от того, является ли оно низким, средним или высоким по сравнению с минимальным и максимально возможным значением.
Сначала я определяю разницу между максимальным и минимальным значениями; в этом случае d3 - a3 = 12.

Оттуда первая 1/3, или 33% от значения 12, является «низким» диапазоном.

Мне удалось использовать «условное форматирование» в Excel и сгенерировать следующую формулу в разделе «Новое правило» → «Использовать формулу, чтобы определить, какие ячейки форматировать»:
1-е правило для «низких значений», чья ячейка будет заполнена определенным цветом; они варьируются от 8 до 12:

=$C2<ROUNDUP(($D2-$B2)*0.33, 0)+$B2

Второе добавленное правило для «высоких значений», то есть в диапазоне 17-20:

=$C2>$D2-ROUNDUP(($D2-$B2)*0.33, 0)

3-е добавлено, для «средних значений», которые не относятся ни к высоким, ни к средним:

=OR($C2>=ROUNDUP(($D2-$B2)*0.33, 0)+$B2, $C2<=$D2-ROUNDUP(($D2-$B2)*0.33, 0))

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

Я хотел бы, чтобы конфигурация "условного форматирования" использовала цвета, назначенные одной из этих 3 соответствующих ячеек, на случай, если в будущем я решу использовать другие цвета; этот переход будет происходить быстрее, чем переходить к каждой отдельной ячейке для изменения цвета.

Answer 1

Я придумал решение, которое использовало как VBA, так и обычный интерфейс Excel.

У меня была ячейка с функцией = SUM, которая помечала все отдельные ячейки, формат которых я хотел бы обновить.

Ячейка (в данном случае C2), формат которой я хотел имитировать, вызывалась со следующим:

Диапазон ("С2").копия

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

cellLocations = Range("A30").формула

А затем использовал соответствующую функцию подстроки & replace, чтобы иметь ее в правильных макетах (заменить + на запятую (,) и удалить круглые скобки & = SUM) для следующей функции PasteSpecial, которая использует "Format Painter":

Диапазон (cellLocations).PasteSpecial Paste:= xlPasteFormats

Примером переменной cellLocations в соответствующем макете является:

А2, В2, В5, С9, Н8

Запятая нужна как разделитель / разделитель.

Любое лучшее или более простое решение будет оценено.

Answer 2

Если вы можете использовать VBA, у меня есть решение.

Это проверяет каждую ячейку со значением в столбце C на соответствие правилам. Какое бы правило ни соответствовало, оно копирует цвет фона из контрольной ячейки в ячейку, которую проверяет. Это будет происходить каждый раз, когда значение добавляется или удаляется из рабочей книги.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    For Each Cell In Range("C3").Resize(Worksheets(1).Cells(Rows.Count, "C").End(xlUp).Row - 2, 1)
        lCell = Cell.Offset(0, -1)
        rCell = Cell.Offset(0, 1)
        If Cell < WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) + lCell Then
            Cell.Interior.Color = Range("F3").Interior.Color
        Else
            If Cell > rCell - WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) Then
                Cell.Interior.Color = Range("F4").Interior.Color
            Else
                If WorksheetFunction.Or(Cell >= WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0) + lCell, Cell <= rCell - WorksheetFunction.RoundUp((rCell - lCell) * 0.33, 0)) Then Cell.Interior.Color = Range("F5").Interior.Color
            End If
        End If
    Next Cell
End Sub