Format cells based on legend

[ZIXILPLIX]

I have a schedule in Excel. On Sheet1 I have a legend that dictates cell colors based on value.
Examples:
cell value = 1, cell is grey and text is bold
cell value = 1T, cell is grey
cell value = 1F, cell is grey
cell value = 2 cell is blue and text is bold
cell value = 2T cell is blue
cell value = 2F cell is blue
cell value = 3 cell is green... etc
There are about 10-15 different formats that I use.

On Sheet2, there are hundreds of cells that have various number and letter combinations, I've used conditional formatting to make them match the legend, but I've had to use numerous rules to make it work. I've had to make a different rule even for 1T and 1F because I can't seem to make some sort of OR statement. What I'd like to do is have a macro that does the work since people keep messing up the conditional formatting in the cells. Please help.

 

[TakenItEasy]

Yeah, if people need to make edits to cells with conditional formatting, than doing things like cut and past can really scramble your formatting regions up.

Here's a good reference for using macros to edit cell formatting.
Excel VBA Formatting Cells and Ranges Using the Macro Recorder | VBA Programming Service

 

[yesterdays]

try this :

Sub test()    Dim Legend As Range
    Dim c As Range
    Dim o As Range
    Set o = Application.Selection
   
    For Each c In o
    
     Set Legend = Worksheets("Sheet1").Range("A1:A30").Find(c.Value)
                                
                                
                    
        
    Legend.Cells(1, 1).Copy
    c.Cells(1, 1).PasteSpecial Paste:=xlPasteFormats
    On Error Resume Next
    Next
    MsgBox ("Done")
End Sub

select the range you want to lookup
then run the code

 

[ZIXILPLIX]

That works pretty well. Thanks for looking at this for me. The only thing is, the format I have in the Legend for 1, R, C, and A aren't coming out right.

What it appears to be doing is:
For 1, it is using the format for 1A
For R, it's using the format for LR
For C, it's using the format for NC
And for A, it's using the format for 1A.

So it seems that if a legend box has two letters, if you use either of those letters singularly in a cell, it defaults to the format of the cell with two characters. Even if you have a cell in the legend formatted for the single character.

Also, is there a way to make the macro run automatically upon entering text in a cell, rather than highlighting the particular cells and running the macro? In other words, on Sheet two, if I were to type "TDY" in cell S17, as soon as I exit that cell, have it run and apply the correct format from the legend. Any help would be greatly appreciated.
See images below for graphical depiction.

[/URL][/IMG]
This is the legend I have set up on Sheet 1

[/URL][/IMG]
This is how Sheet 2 looks after I run the Macro. Note that cells with 1, R, C and A don't match the legend.

 

[yesterdays]

Okay Paste this in your Sheet2 code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Application.EnableEvents = False


    Dim Legend As Range
    Dim c As Range
    Dim o As Range
    Set o = Target 'Application.Selection
   
    For Each c In o
    
     Set Legend = Worksheets("Sheet1").Range("A1:A30").Find(c.Value, lookat:=xlWhole, MatchCase:=True, SearchFormat:=False)


    Legend.Cells(1, 1).Copy
    c.Cells(1, 1).PasteSpecial Paste:=xlPasteFormats
    On Error Resume Next
    Next
    'MsgBox ("Done")


ErrHandler:
  Application.EnableEvents = True
End Sub

 

[ZIXILPLIX]

Genius yesterdays! Thanks a lot!

 

[yesterdays]

You are welcome : )

 

[ZIXILPLIX]

New wrinkle. Is there any way to make the code I pasted in Sheet2 only apply to a range of rows in the sheet rather than all cells? For example, have it apply to rows 16-22 and 27-31, but if I type one of the Legend codes in cell J24 have it not reference the Legend?

Also, if it is possible, if I add a new row between 16-22, I'd want it to apply to the new row as well. This is for an office calendar and people are always coming and going. I don't ask too much, do I? ha ha Thanks again for any help you're able to provide.

 

[mpab111]

try this :

Sub test()    Dim Legend As Range
    Dim c As Range
[/QUOTE]
Hi Yesterdays, thanks for that. I have a little extra request: 
Problem: in my case some of the cell contents in the target (data) range will not match the legend. Basically, my legend is composed of codes using letters. But some of the target cells have numerical (general/date) contents.
Suggested solution: I would like the macro to skip the cells that don't match any of the codes that are present in the "legend" range.

Regards

 

[mpab111]

Forgot to say:

1) the line "On Error Resume Next " looks like it should handle what I am asking for, right? But no, it comes up with error message 91