How do I make Sheet Code available in all sheets

I found sheet code on the forum that highlights the both the entire row and column when a cell is selected.

This is great, but is there a way I can make this an add-in so it is easily available in all workbooks and sheets?

Thanks!

Code:

Please Login or Register  to view this content.

Hi,

Use the

Please Login or Register  to view this content.

event. If it's just you that needs to use this then you could stick it in your Personal.xls workbook which is always resident in memory.

Otherwise put it in a blank workbook and then save the workbook as a .xla add-in.

HTH

Thanks Richard

I put the following code in Personal.xlsb, and it doesn't work...Sub

Please Login or Register  to view this content.

How should I do it, as a module? (Please note - I'm a noob when it gets to VBA!)

Hi,

Change that to

Please Login or Register  to view this content.

If you put it into the Personal macro workbook, it will only work for sheets in that workbook (not much use as it's hidden) unless you add an application-level event handler. I'd just use Chip Pearson's one from here personally...

Sorry, just realised there's some confusion.

Those two lines of code will only work when they are in the Workbook_SheetChange event of the activeworkbook, which is not what you're after since you want it to run in whatever is the active workbook

You can run a macro in the Personal.xls workbook but to do that you need something to trigger the macro. i.e. a Button or Ctrl shortcut key.

Go to the Personal.xls workbook in the Visual Basic Environment, Choose Insert Module unless there's one there already.

Now add the following macro.

Please Login or Register  to view this content.

Now back in Excel choose Tools Macro Macros, select the Personal.xls workbook and click the 'HighlightRowColumn' macro.

Then click the options button and enter a shortcut key, say 'h' for highlight.

Now whenever you click Ctrl-h the row and column for the active cell in the active workbook will be highlighted.

HTH

Was just about to post "Ouch, WTF am I doing wrong??" Losing the "undo" facility (Pearson Rowliner does that) is not nice...

Follow up: How do I get rid of the previous "highlighting" when I run the macro in a different cell? In some case leaving it would be useful, in some case not... maybe two macros, one for times you want highlighting to remain, one if you don't?

Hi,

You could add an additional first line

Please Login or Register  to view this content.

This will first clear any highlights on the sheet before re-applying to the active cell.

Create two macros one as before and one with this additional line and give them different short cut keys.

HTH

Hi,

You could experiment with code like the following at the top of the procedure.

Please Login or Register  to view this content.

This will clear rows that don't have a colour 8.
You'll need to add a similar For..Next statement for the columns.

HTH