Calling

[Dylancam]

Hi Gurus,

This is my first post so if I'm not playing by the rules, I'm happy to take on any feedback...

So I have a code that I use frequently that I want to place in my "personal macro workbook" so I can call it quickly without re-writing it each time I wish to use it. The code simply highlights the row within a table which has been selected. It is accomplished as follows:

On the Worksheet:
1) "SelRow" is typed in cell CA1
2) "SelCol" is typed in cell CB2
3) CA1:CB2 are selected
4) In the "Formulas" tab "Create from Selection" is clicked
5) Checkbox "Top Row" is selected and "OK" is clicked

In The Visual Basic Editor
1) The active sheet is selected in the sidebar
2) The Drop Down box on the left is changed from "General" to "Worksheet"
3) The Drop Down box to the right is changed to "SelectionChange"
4) 2 lines of code are added resulting in the below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[SelRow] = ActiveCell.Row
[SelCol] = ActiveCell.Column
End Sub

Back to the Worksheet
1) Back in the worksheet, the range where the active row highlight is applicable in this case e5:j12 (I would like the new code to define this range by whatever range is selected when the procedure is triggered)
2) Navigate as follows "Home", "Conditional Formatting", "New Rule"
3) Select "Use a formula to determine which cells to format"and type the formula " =ROW(B5)=SelRow"
4) Then click "format" and select a fill colour to use as a highlight

So..... That is the procedure I use currently. Whenever I select a cell on a different row. The row inside the defined range is highlighted by the fill colour until I select another row.

Where I am struggling is how to place this in the personal macro workbook so I can trigger it on any workbook I create or wish to edit without the need to step through the process I have outlined.

Any help will be greatly appreciated ray:

Thanks,
Dylan

Using Excel 2010....

 

[BarryL]

hi Dylan,

if you have already added to your personal macro workbook, follow the following steps

1)Go to your quick access toolbar (ICYDK this is found either above or below the ribbon where the floppy disk for saving is and the add new document is)
2) select the drop down to customize the quick access toolbar (its a line above an upside down triangle)
3) select more commands
4) from the choose commands from drop down box select "Macros"
5) here should lie your macro with PERSONAL.XLSB! before it.
6)click on it and press the Add button.
7) click ok and you will now see this as a button on your toolbar that will be there no matter what doc you are in in excel.

any problems let me know

 

[Dylancam]

Hi Barry,

Thanks for the response.

The issue is around the actual function of the code as it is executed in a workbook (personal workbook) other than the workbook that I want the actions to apply to. I think the main issue is how to manage the "Worksheet_Selection_change (ByVal Target as Range)" as it is looking at the selection change from the personal workbook rather than the active workbook that I want the code to run on...

I am cool with adding Macros to the QAT. I have a fair few of them up there currently.

I hope I have given some clarity to my issue

Thanks again for your response

 

[starl]

DylanCam - rule #1 - don't post the same question more than once. Your duplicate has been removed.