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

 

[Jaafar Tribak]

Jaye7.

When you say activecell does that mean the activecell in whichever worksheet happens to be active or the active cell in a specific worksheet ?

 

[John Davis]

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

Perhaps a Workbook-SheetSelectionChange Event?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("A1:B500")) Is Nothing Then

        Target.Interior.ColorIndex = 3
Else

        Target.Interior.ColorIndex = 6
End If
End Sub

 

[Jaye7]

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.

 

[Colin Legg]

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.

I haven't checked John's code (logic, error handling etc) but he did provide you with a workbook level event handler (ie. Workbook_SheetSelectionChange) which would go in the ThisWorkbook code module. You would not need to paste it into every sheet of every workbook; it will cover all the sheets within that workbook.

If you require something at an application level (ie. all workbooks) then the code will need a little tweaking.

 

[Jaye7]

Thanks Colin, I should have read John's script properly, however I want the script to not have to rely on selection change as I want to run the script as a normal macro when I click on the macro, I don't want the worksheet to change automatically every time I am within the range as some worksheets I don't want to change at all.
I don't know how to explain exactly what I am after, but just that it relies upon myself executing the code rather than the selection change executing it as it could cause majorly hassles on work sheets that I don't want changed.
As stated the cell colour thing is just an example it will actually be running a lot of code which could have devastating effects if it is activated in the wrong worksheet.
Thanks

 

[Colin Legg]

Hi,

I understand. You could just have a toolbar button (or something on the ribbon if using XL2007+) with a bit of VBA code attached which will check the activesheet whenever you click on it?

 

[Jaye7]

Colin,

I am using 2003 and really want a macro that will check if the activecell is within the specified range, I want to use it for various scripts and different ranges for different projects.

 

[Colin Legg]

Yes, I'd say a toolbar button would be ideal for this? It can sit there on your toolbar all the time and, whenever you click on it (ie. click on the button), it will call a procedure which will do whatever you code it to?

 

[Jaye7]

Colin,

It would be great for calling one procedure, however I will be using multiple procedures and also my files are in different folders on different computers, i.e. at work it is in one folder and at home it is in a different folder which buttons don't accomodate for, I am after an easy solution that I can use.