asked on

Excel 2016 -Legend for spreadsheet the uses fonts and highlighting

Excel 2016/Win10:

Hi,

I'm creating a spreadsheet (a novel "scene schedule") with the following attributes:
1. Any row referring to a flashback scene is in italics.
2. Any row referring to a scene that's part of a new sub-plot is highlighted with yellow background.
3. etc.

Is there an easy way to add a readily visible legend? A quick-and-dirty way would be to put it in another worksheet, but I'd rather not make the editor helping me jump back and forth between two sheets.

Thanks,
Steve

PS-If the answer is "Can't be done.", that's fine, and I'll credit you with points. :)

Martin Liss

Can you supply a manually done sample of what you are looking for?

Stephen Kairys

ASKER

Here you go. Thanks!

Martin Liss

I'm not sure of what your goal is since you demonstrated that you manually do what you want. Are you saying that if any row on the sheet contains, for example, the text "Potential subplot" that the row should be made yellow?

Stephen Kairys

ASKER

Nope. I just wanted a chart that could (e.g.) overlay the page in question and display the legend I outlined. In other words, more seamless than manual. But, as I said, I'll fall back to doing it manually if there is no easy way to display both the legend and the scene schedule at the same time. :) Thanks.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Kairys

ASKER

For some reason, this file does not seem to open. However, it might be useful for cells to auto-color when I type something like "Flashback scene." What steps did you take to configure?

As to my original question, I'll go with either the separate sheet for the legend, or shoehorn it into the right side of the main sheet.

Thanks.

Martin Liss

I only have Excel 2010 so that's probably why it won't open. Here's the code which refers to a sheet called Legend.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Select Case True
    Case InStr(1, UCase(Target), UCase(Sheets("Legend").Range("B2"))) > 0
        Sheets("Legend").Range("A2").Copy
        Cells(Target.Row, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Case InStr(1, UCase(Target), UCase(Sheets("Legend").Range("B3"))) > 0
        Sheets("Legend").Range("A3").Copy
        Cells(Target.Row, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Case InStr(1, UCase(Target), UCase(Sheets("Legend").Range("B4"))) > 0
        Sheets("Legend").Range("A4").Copy
        Cells(Target.Row, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Case Else
        Range("Z1").Copy
        Cells(Target.Row, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End Select

Range("A1").Select
Application.EnableEvents = True
End Sub

Open in new window

Here's a picture of the Legend sheet.

You could create a macro that responds to, say, Ctrl+Shift+L. After that if the user selects some area on the main sheet and presses Ctrl+Shift+L, a userform would appear that displays the 3 formatting choices and upon selecting one of them the selected area on the sheet would be appropriately formatted using some of my code.

Stephen Kairys

ASKER

Tell you what...I've had issues with inserting macros into my workbooks. So, I'm going to accept one of your comments suggesting a simple solution per the legend. This conversation has made me realize it's not worth the effort to do something fancy, so it's helped me. Thank you.

Stephen Kairys

ASKER

Thanks again. Please note that I did not get to try out the attached spreadsheet. Have a good day.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

Stephen Kairys

ASKER

Thanks again, Martin. Have a good evening.