Determine if a cell is within a range using VBA in Microsoft Excel 2010

In Microsoft Excel, we can determine if a Cell is within a Range with IF Function, however, when it comes to identify the same via VBA code then we need to use if statement. Below is the VBA code and process which you need to paste in the code module of your file.

1. Open Excel
2. Press ALT + F11
3. The VBA Editor will open.
4. Click anywhere in the Project Window.
5. Click on Insert
6. Click on Module

 

Image1

 

7. In the Code Window, Copy and Paste the below mentioned Code

 

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
Set InterSectRange = Application.InterSect(Range1, Range2)
InRange = Not InterSectRange Is Nothing
Set InterSectRange = Nothing
End FunctionSub TestInRange()
If InRange(ActiveCell, Range("A1:D100")) Then
' code to handle that the active cell is within the right range
MsgBox "Active Cell In Range!"""
Else
' code to handle that the active cell is not within the right range
MsgBox "Active Cell NOT In Range!"""
End If
End Sub

 

you22

 

8. Once this is pasted, go to the Excel file
9. Select cell A1
10. Click on the VIEW Tab on the ribbon
11. Click on Macros
12. Click on View Macros
13. Shortcut Key to View Macros is ALT + F8
14. A Window will popup

 

you33

 

15. Select the Macro
16. Here the Macro is named as “TestInRange”
17. Select the Macro “TestInRange”
18. Click on Run
19. As this cell is in Range you will get a Popup which says “Active Cell In Range!”

 

image4

 

20. Click OK to close the Box

 

Now we will select Cell G9 which is not in Range

1. Select cell G9
2. Click on the VIEW Tab on the ribbon
3. Click on Macros
4. Click on View Macros
5. Shortcut Key to View Macros is ALT + F8
6. A Window will popup

 

yo55

 

7. Select the Macro
8. Here the Macro is named as “TestInRange”
9. Select Macro “TestInRange”
10. Click on Run
11. As this cell is not in Range you will get a Popup which says “Active Cell NOT In Range!”

 

image6

 

12. Click OK to close the Box

This is how we can determine whether a Cell is within a Range or not using VBA.

 

image 48

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube