VBA to search column when cells change

[DrH100]

Hi all
Hope someone can help with what I trying to do.

What I would like (and have no idea where to start) is when a user updates a cell in my worksheet that the code looks at the cells at the top of that column and if one is greater than the other produces a message box.

Ie: User updates cell AA33 (or any cell in that column). If cell AA1 is greater than cell AA2 a message is displayed. If it's not all is good until the next cell is changed.

I would like this to happen no matter what column the user inputs in although the max range is likely to be something like E28 to BB128 if that helps.

Any help appreciated, as I say I have no idea where to start or even if my description makes any sense.

Thanks in advance.

 

[Joe4]

Yes, that can be done with a Worksheet_Change event procedure, which is VBA code that runs automatically when a cell is updated manually.
However, I think we need a little clarification. When a cell is updated, are we always comparing the value of row 1 in that column to row 2?
If not, please explain the exact logic of how we can determine which cells we are comparing.

 

[DrH100]

Thanks Joe4

That's correct. It will always be the 2 cells at the top of the column the user updates. Essentially those cell contain various counts of the information in the column.

Hope that makes sense.

Thanks again

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select View Code, and then paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim col As Long

'   Set range to apply this to
    Set myRange = Range("E28:BB128")

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit if cell updated is outside of designated range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Get number of updated column
    col = Target.Column
    
'   Check to see if row 1 is greater than row 2 in that column
    If Cells(1, col) > Cells(2, col) Then
        MsgBox "Row 1 is greater than row 2 in this column"
    End If

End Sub

This should do what you want automatically when you manually update a cell in the range E28:BB128.

 

[DrH100]

Thanks Joe4

Works perfectly.

 

[Joe4]

You are welcome.