Исправление цвета ячеек в Excel при изменении значений этих ячеек

Question

Вот пример 4 ячеек в моей книге.

26  99
2   1

Рабочая книга окрашена в соответствии с правилами 100 = красный 0 = желтый, где элементы ближе к 0 более желтые, а элементы ближе к 100 более красные (это определяется условным правилом форматирования, которое назначает цвета на основе значения).

В этом примере 99 будет очень красным, 1 и 2 будут очень желтыми, а 26 будет иметь оранжевый оттенок.

Я хочу, чтобы цвет ячеек оставался неизменным, но меняю значения в ячейках. В этом примере я хочу, чтобы они были

E=0.0,Q=26  E=0.0,Q=99
E=2.10e-11,Q=2  E=1.02e-08,Q=1

(Обратите внимание, что значения в правой части q такие же, как в первой матрице)

Из-за правила условного форматирования мои цвета исчезают, когда я вставляю в них новые значения.

Answer 1

Вы всегда можете добавить 100 условий форматирования на основе значения после Q= в E=0.0,Q=99 . Вот макрос, который будет делать это для всех выбранных вами ячеек, сравнивая каждое пустое значение ячейки с 1 до 100 напрямую или сравнивая его со значением после Q= если это первое сравнение приводит к ошибке.

Вы можете удалить макрос после запуска, чтобы вам не нужно было сохранять файл как XLSM. Раскомментируйте выделение Selection.FormatConditions.Delete строку, удалив апостроф, если вы также хотите удалить текущие условия формата.

Это предполагает, что ваше число всегда является целым числом от 1 до 100 (либо в качестве значения пустой ячейки, либо после Q=). Это не будет работать для десятичных значений.

Sub FormatMe()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    'Selection.FormatConditions.Delete
    Dim addr As String
    addr = Replace(Selection.Cells(1).Address, "$", "")
    For i = 1 To 100
        With Selection.FormatConditions.Add(xlExpression, Formula1:= _
            "=OR(" & addr & " = " & i & ", IFERROR(RIGHT(" & addr & _
            ", LEN(" & addr & ") - FIND(""Q"", " & addr & ", 2) - 1) = """ & i & """, FALSE))")
           .Interior.Color = RGB(255, 255 - Int(i / 100 * 255), 0)
        End With
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Answer 2

• Предположение: вы хотите использовать условное форматирование (а не VBA)

• Предположение: ячейка, которую вы хотите закрасить, содержит "сложные" значения, такие как «E = 0.0, Q = 26»

• Ответ: выберите один - условное форматирование Excel недостаточно умно, чтобы "прочитать" значение «E = 0.0, Q = 26» и найти число 26.

  • Использование вашей собственной формулы форматирования может сработать, но это не может применить градиент, поскольку он выполняет только один цвет для каждого условия, иначе говоря, «если число равно 29, сделайте его таким точным цветом». (Насколько я знаю - поправьте меня пожалуйста)
  • Excel может автоматически извлечь число 26 из вашей ячейки, и эту (соседнюю) ячейку можно отформатировать (используя ячейку F4 =NUMBERVALUE(RIGHT(F4,LEN(F4)-FIND(",",F4)-2)))

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

Answer 3

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