Excel: conditionally format a cell using the format of another, content-matching cell

conditional formattingmicrosoft excelvbaworksheet-function

I have an Excel spreadsheet where I’d like to be able to create a “key” of formatted cells with unique values, and then in another sheet format cells using the key formatting.

So for example, my key is as follows, with one value per cell and the visual formatting indicated in parentheses:

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

So that’s on one sheet (or in a remote corner of the current sheet—whichever is better). Then, in an area that I mark for conditional formatting, I can type one of those three letters and have the cell where I typed it visually formatted according to the key. So if I type a “B” into one of the conditionally formatted cells, it gets a green background.

(Note that I’m using backgrounds here solely for ease of explanation: ideally I want to have all visual formatting copied over, whether it’s foreground color, background color, font weight, borders, or whatever. But I’ll take what I can get, obviously.)

And—just to make it extra-tricky—if I change the formatting in the key, that change should be reflected in cells that reference the key. Thus, if I change the “B” formatting in the key from a green background to a purple background, any “B” in the main sheet should switch to the new color. Similarly, it should be possible to add or remove values from the key and have those changes applied to the main data set.

I’m okay with the formatting-update-on-key-change being triggered by clicking a button or something. I suspect that if any of this is possible it will require VBA, but I’ve never used it so I’ve no idea where to start if that’s the case. I’m hoping it’s possible without VBA.

I know it’s possible to just use multiple conditional formats, but my use case here is that I’m trying to create the above-described capability for someone who isn’t conversant with conditional formatting. I’d like to let them be able to define a key, update it if necessary, and keep on truckin’ without me having to rewrite the spreadsheet’s formatting rules for them.

— UPDATE —

So I think I was a bit unclear about my original request. Let me try again with an image.

Excel screeshot

The image shows the “key” on the left, where values and styles are defined using keyboard and mouse input. On the right, you see the data that should be formatted to match the key.

Thus if I type a “C” into a cell in the Data area, it should be blue-backed. Furthermore, if I change the formatting of “C” in the Key to have a purple background, all the “C” cells should switch from blue to purple. For further craziness, if I add more to the Key (say, “D” with a yellow background) then any “D” cells will be styled to match; if I remove a Key entry, then matching values in the Data area should revert to default styling.

So. Is that more clear? Is it possible, in whole or in part? I don’t have to use conditional formatting for this; in fact, at this point I suspect I probably shouldn’t. But I’m open to any approach!

Best Answer

This uses cells in A1 to set the condition for cells in D1:D9 - alter ranges to suit your needs:

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

After changing the color, you will then have to change the cell by editing it, and pressing enter (so the change event is triggered)
the format conditions are ordered by the order the rules are applied. There are a lot more changes that could be applied, just add another .FormatConditions(1).Whatever = Target.Whatever to the code

This code does not set up any conditions, it will just alter the one(s) that are there. The conditions are numbered in the rule order displayed on screen

if you don't want to use conditional formatting, and just color the cells, then you can loop over each cell this way:

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
Related Question