Excel: conditionally format a cell using the format of another, content-matching cell
- by Eric A. Meyer
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.
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!
