Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
    
Take a look at the VBA conditional formatting questions on stackoverflow. There should be enough examples there to give you what you need: stackoverflow.com/search?q=vba+conditional+formatting –  Rhys Gibson Sep 11 '12 at 20:53

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
    
That looks most promising, Sean—thanks! I’ll try it out as soon as I can and report back with the results (and, hopefully, an upvote/check). –  Eric A. Meyer Sep 12 '12 at 1:34
    
Unfortunately I can’t get it to work: at first any changes to $A$1 got me a “error 9 subscript out of range”, and now I can’t trigger any errors or changes. If I remove the “Private” the macro still doesn’t show up in the Macros list, so I can’t run it manually to see if I still get an error or not. I am pretty green at VBA and macros, so it’s easily possible I’m doing something dumb, but simple macros seem to run fine. Kinda stumped—sorry! –  Eric A. Meyer Sep 12 '12 at 2:41
    
Followup: my first sheet is in fact called “Sheet1” (I didn’t change it). I copy-and-pasted the given VBA directly into Sheet1’s code window under (General). There is a newline after End Sub. I’m using OS X Excel 2011. –  Eric A. Meyer Sep 12 '12 at 2:52
    
this code will go into the workbook - look just above the module part of your project window (where it has Microsoft Excel Objects followed by 'Sheet1','Sheet2', etc. The last one is ThisWorkbook - put the code in there –  Sean Cheshire Sep 12 '12 at 3:35
    
Okay, putting it there (instead of in Sheet1; thanks!) got me back to the error: “Run-time error 9: Subscript out of range”. When I hit “Debug” it highlights the line Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color. –  Eric A. Meyer Sep 12 '12 at 20:48

Here’s an extension of what Sean did (just showing the interior of the 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.