29. VBA Tips - Find Out If A Cell Is Within A Range

Nick's picture


This tip is about finding out whether a cell is within a range.

  • It's basic set theory... we use VBA's INTERSECT

What we want to write is some VBA code that tells us whether we have selected a cell within the data table:

find-out-if-a-cell-is-within-a-range

Here's the code we use here:

find-out-if-a-cell-is-within-a-range

 

 

Explanation

  1. Using Worksheet_SelectionChange we trap the event that the selection has been changed
  2. We then set a variable InRange to equal an object returned by the intersect function
  3. If the interset function doesn't find an intersect, it returns NOTHING
  4. We then check whether InRange is equal to NOTHING...
    • If it is, the the user has not selected a cell within the range.
    • If it's equal to something, then the range is within MyData

Download sheet to practise how to Find Out If A Cell Is Within A Range in Excel

Training Video on how to Find Out If A Cell Is Within A Range in Excel:

AttachmentSize
find-out-if-a-cell-is-within-a-range.xls66 KB