Microsoft KB Archive/64090

{| = Excel Macro to Determine if Active Cell Is in a Named Range =
 * width="100%"|

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(&quot;In group 1&quot;)) 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).
 * }