if activecell not in range

[Jaye7]

Hi, can someone please help.

I am after a script that for instance, if the activecell is not within range A1:B500 then it will color the cell yellow.

If the activecell is within the range then it will color the cell red.

I do not want to use the worksheet selection change event as I want to be able to run the script in any worksheet at any time, the color of the cell yellow is just an example this is not actually what my script will do but if someone can help with the basics then I will modify it to suite.

Thanks

 

[Colin Legg]

Hi Jaye,

Sorry, but you keep moving the goalposts so it's hard to give an acceptable solution! So we're looking at multiple procedures for multiple worksheets in multiple workbooks on multiple computers in multiple locations?

You can have a set of buttons on a toolbar, each of which calls the procedure you need. There are alternatives to this such as using a dropdown menu. These sorts of ideas mean you don't have to look around to try to find the code when you need it: they provide an interface. For "communal" code such at this it basically either needs to be stored in personal.xls or in an add-in (.xla).

For example, you can use an add-in to install the toolbar and contain the underlying procedure code. This would load everytime you start Excel. At work that add-in could be on a shared, network drive and accessed by multiple computers. You'd have to keep a copy of it at home if you don't have access to the one at work from there.

Are we getting closer?

 

[Jaye7]

Thanks for hanging in there Colin,

The following is not intended to be rude.

It is hard when (TO ME) my request seems simple, I just want a code that is executed via a macro (which is on a userform, but that's not important as I will adjust the script) when I click on the macro it we check if the active cell is within the range, if it is then it will do one thing, if it's not then it will do something else.

Most macro scripts that I run are based on the active worksheet that I am in as then I can use them whenever and where-ever, but don't want it to run based on selection change.

Thanks Colin

 

[Colin Legg]

Hi Jaye,

Having code check the activecell / activesheet is simple. That is not the problem here and it can be addressed in 2 seconds.

The difficult part is you want the code universally available: not when a particular workbook is open and not just on a particular computer. It is this more difficult aspect which I am addressing and the solution I have suggested is to use an add-in or personal.xls to hold the procedures. As an interface you can have a commandbar (for example) with buttons which will call the procedures, show the userform, or whatever it is that you need. I am not suggesting that you use a worksheet change event handler for this.

Unfortunately I'm not managing to communicate it in a way which is meaningful to you, so I'll apologise and gracefully bow out and let someone else take up the gauntlet.

Best of luck with your project!

 

[John Davis]

Hi Jaafer, yes it will be the activecell within whatever worksheet that I am in at the time, the cell that is selected in the worksheet.

John, thanks for the script, however as I am using the script within many different worksheets the worksheet selection change is not what I want as I would have to paste it into every single sheet of every workbook that I am using the script in and that would be painful.

If the worksheets are in the same workbook, then you only have to paste in the ThisWorkBook sheet change event. Go to the Visual Basic Editor - Your VBA Project - ThisWorkbook-Then place the code in the Workbook_SheetSelectionChange event.

 

[John Davis]

If the worksheets are in the same workbook, then you only have to paste in the ThisWorkBook sheet change event. Go to the Visual Basic Editor - Your VBA Project - ThisWorkbook-Then place the code in the Workbook_SheetSelectionChange event.

Sorry, I didn't notice the other posts before I responded.