Excel Macro to Determine if Active Cell Is in a Named Range

 Last reviewed: November 2, 1994
Article ID: Q64090
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
  • Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

SUMMARY

The following macro brings up an alert box if the active cell is in a named range on the active sheet. In other words, if the active sheet is Sheet1 and the name Group1 is defined on Sheet1, this macro will tell you if the current active cell on Sheet1 is within the range named Group1.

Make sure to include the space between ACTIVE.CELL() and !group1.

Macro

   A1: InRange
   A2: =IF(ISERROR(ACTIVE.CELL() !Group1),,ALERT("In group 1"))
   A3: =RETURN()
NOTE: You can use the above macro in Microsoft Excel 5.0 if you use it in a Microsoft Excel 4.0 Macro sheet.

MORE INFORMATION

This macro uses the intersection operator (the space) to check if there is an intersection between the active cell and the named range (Group1). If there is an intersection, the intersection operation returns the contents of the active cell, which causes the ISERROR() function to return FALSE. Because the argument of the IF statement is FALSE, it executes its second argument (the alert box). If there is no intersection, the intersection operation will return #NULL!. This causes ISERROR() to return TRUE, and the IF statement executes its first argument (which in this example, is not specified).

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00
3.0 4.00 4.0



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.