Format cells based on legend

ZIXILPLIX

Board Regular
Joined
Nov 12, 2014
Messages
56
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
try this :
Code:
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
 
Upvote 0
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.

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

Sheet2.jpg
[/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.
 
Last edited:
Upvote 0
Okay Paste this in your Sheet2 code
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
 
Upvote 0
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.
 
Upvote 0
try this :
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top