CODEWITHSUNDEEP
vbaexcel
Selim Erkan
Selim Erkan

Reputation: 67

Show a message when specific column's cell change

I am new on Excel VBA. I want to display a warning message when any cell in K column changes.
I wrote this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim row As Integer
    Dim lrow As Long
    With ActiveSheet
        lrow = .Range("K" & .Rows.Count).End(xlUp).row

        For satir = 5 To lrow
            If Cells(row, 11).Value > 400 And Cells(row, 12).Value = "" Then
                MsgBox _
                ("Risk Point of your operation is still high, Please Identify Contingency Plan")
            End If
        Next row

        For row = 5 To lrow
            If Cells(row, 22).Value > 200 And Cells(row, 24).Value = "" Then
                MsgBox ("Risk Point is very high, Please Identify your Mitigation Plan")
            End If
        Next row
    End With

End Sub

This code is working but shows a warning message on all changes made in the worksheet.

Upvotes: 2

Views: 286

Answers (2)

L42
L42

Reputation: 19727

This is refactoring your code. This answer by C. Henke already answered your question.

Dim row As Long '~~> use long to avoid overflow
Dim lrow As Long, satir As Long

With Me '~~> to refer to the worksheet itself, you can use Me object

    If Not Intersect(.Range("K:K"), Target) Is Nothing Then
    '~~> check if target cell reside in K column
        lrow = .Range("K" & .Rows.Count).End(xlUp).row
        For satir = 5 To lrow
            If .Cells(row, 11).Value > 400 _
            And .Cells(row, 12).Value = "" Then
                MsgBox "Risk Point of your operation is still high." & _
                vbNewLine & "Please Identify Contingency Plan"
            End If
        Next satir

        For row = 5 To lrow
            If .Cells(row, 22).Value > 200 _
            And .Cells(row, 24).Value = "" Then
                MsgBox "Risk Point of your operation is still high." & _
                vbNewLine & "Please Identify Contingency Plan"
            End If
        Next row
    End If

End With

Hope this gets you going.

Upvotes: 1

C. Henke
C. Henke

Reputation: 161

Write this into your Worksheet_Change Sub:

 If Target.Column = 11 Then
    MsgBox "warning"
 End If

This will send "warning" as soon as the user changes a Value in column k

Upvotes: 3

Related Questions