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

Question

У меня есть электронная таблица Excel, где я хотел бы иметь возможность создавать «ключ» отформатированных ячеек с уникальными значениями, а затем в ячейках формата другого листа, используя форматирование ключа.

Так, например, мой ключ выглядит следующим образом, с одним значением на ячейку и визуальным форматированием, указанным в скобках:

A (red background)
B (green background)
C (blue background)

Так что это на одном листе (или в удаленном углу текущего листа - в зависимости от того, что лучше). Затем в области, которую я отмечаю для условного форматирования, я могу набрать одну из этих трех букв и получить ячейку, в которой я набрал ее, визуально отформатированную в соответствии с ключом. Поэтому, если я введу «B» в одну из условно отформатированных ячеек, он получит зеленый фон.

(Обратите внимание, что здесь я использую фон только для простоты объяснения: в идеале я хочу скопировать все визуальное форматирование, будь то цвет переднего плана, цвет фона, размер шрифта, границы или что-то еще. Но я возьму то, что смогу получить, очевидно.)

И - просто чтобы сделать его более сложным - если я изменю форматирование в ключе, это изменение должно быть отражено в ячейках, которые ссылаются на ключ. Таким образом, если я изменю форматирование «B» в ключе с зеленого фона на фиолетовый, любой «B» на основном листе должен переключиться на новый цвет. Аналогично, должна быть возможность добавлять или удалять значения из ключа и применять эти изменения к основному набору данных.

Я в порядке, когда форматирование-обновление-по-ключу-переключение запускается нажатием кнопки или чего-то еще. Я подозреваю, что если что-то из этого возможно, потребуется VBA, но я никогда не использовал его, поэтому не знаю, с чего начать, если это так. Я надеюсь, что это возможно без VBA.

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

--- ОБНОВИТЬ ---

Так что я думаю, что мне немного неясно мой первоначальный запрос. Позвольте мне попробовать еще раз с изображением.

Изображение показывает «ключ» слева, где значения и стили определяются с помощью клавиатуры и мыши. Справа вы видите данные, которые должны быть отформатированы в соответствии с ключом.

Таким образом, если я введу «C» в ячейку в области данных, она должна быть выделена синим цветом. Кроме того, если я изменю форматирование «C» в ключе, чтобы иметь фиолетовый фон, все ячейки «C» должны переключиться с синего на фиолетовый. Для дальнейшего безумия, если я добавлю больше к Ключу (скажем, «D» с желтым фоном), тогда любые ячейки «D» будут оформлены так, чтобы соответствовать; если я удаляю запись ключа, соответствующие значения в области данных должны вернуться к стилю по умолчанию.

Так. Это более понятно? Возможно ли это полностью или частично? Мне не нужно использовать условное форматирование для этого; на самом деле, на данный момент я подозреваю, что не должен. Но я открыт для любого подхода!

Answer 1

Это использует ячейки в A1, чтобы установить условие для ячеек в D1:D9 - изменить диапазоны в соответствии с вашими потребностями:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" and Sh.Name="Sheet1" Then
    Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color
    Sheets(1).Range("D1:D9").FormatConditions(1).Interior.Color = Target.Interior.Color
End If

End Sub

После изменения цвета вам нужно будет изменить ячейку, отредактировав ее и нажав клавишу ввода (чтобы сработало событие изменения)
условия форматирования упорядочены в порядке, в котором применяются правила. Есть еще много изменений, которые можно применить, просто добавьте еще одно .FormatConditions(1).Whatever = Target.Whatever бы в коде

Этот код не устанавливает никаких условий, он просто изменит те, которые есть. Условия нумеруются в порядке правил, отображаемом на экране.

если вы не хотите использовать условное форматирование и просто закрашивать ячейки, то вы можете перебрать каждую ячейку следующим образом:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Or Sh.Name <> "Sheet1" Then Exit Sub
' exit if not in key range (formatting key is A:A on sheet1
If VarType(Target) > vbArray Then Exit Sub
' if multiple cells are changed at once, then exit, as i'm not going to fight with multi cell change

Dim TargetRange As Range
Dim lCell As Object

Set TargetRange = Sh.Range("D1:D9")
' changing cells in this area

For Each lCell In TargetRange.Cells
    If lCell.Value = Target.Value Then
    ' only change cells that match the edited cell
        lCell.Font.Color = Target.Font.Color
        lCell.Interior.Color = Target.Interior.Color
        ' copy whatever you feel needs to be copied
    End If
Next

End Sub

Answer 2

Вот расширение того, что сделал Шон (просто показывая внутреннюю часть Sub):

If Intersect(Target, Range("A:G")) Is Nothing Or Sh.Name <> "Sheet3" Then Exit Sub
' exit if not in key range (formatting key is A:A on sheet1
If VarType(Target) > vbArray Then Exit Sub
' if multiple cells are changed at once, then exit, as i'm not going to fight with multi cell change

Dim KeyRange As Range
Dim TargetRange As Range
Dim lCell As Object
Dim kCell As Object

Set KeyRange = Sh.Range("A1:A10")
' formatting key is here
Set TargetRange = Sh.Range("D1:F9")
' changing cells in this area

For Each kCell In KeyRange.Cells
 If kCell.Value <> "" Then
  For Each lCell In TargetRange.Cells
    If lCell.Value = kCell.Value Then
    ' only change cells that match the edited cell
        lCell.Font.Color = kCell.Font.Color
        lCell.Interior.Color = kCell.Interior.Color
        ' copy whatever you feel needs to be copied
    End If
  Next
  End If
Next