Macro with show messsage box if cell contains value

Question

I would like to do a macro. My testing cells are on another sheet. Sheet - (data) Macro check a range ("D2:D10") if the cells contain value 12 if yes show me a message box "Go to add to system" and this cell where macro found a value will be set to 0.

I have this code but it doesn't work for me I don't known why. Can you help me?

Private Sub check(ByVal Target As Range)
For Each c In Worksheet("data").Range("D2:D10")
 If Range("D2:D10") = 12 Then
    MsgBox "Go to add to system"
    Range ("D2:D10").value = 0
 End If
Next c
End Sub

What if multiple cells have 12? Do you want to change the full range to 0 if even one cell is found 12? — nightcrawler23, Dec 12 '16 at 08:25
@Fiínek Cahů test the code in my answer below and let me know is it works like you intended — Shai Rado, Dec 12 '16 at 14:21

Shai Rado · Accepted Answer · 2016-12-12T09:47:21.603

1

The code below will correct your code (it will run without errors):

Option Explicit

Private Sub check(ByVal Target As Range)

Dim c   As Range

For Each c In Worksheets("data").Range("D2:D10")
    If c.Value = 12 Then
        MsgBox "Go to add to system"
        c.Value = 0
    End If
Next c

End Sub

However, you could go with a slightly different approach - by accomplishing what you are trying to achieve in the Worksheet_Change event (of "data" sheet).

Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c   As Range

' Optional : use if criteria below to check the range only
' if one of the cells inside range("D2:D10") has changed
If Not Intersect(Range("D2:D10"), Target) Is Nothing Then
    ' if you decide to use the "If"above, then you don't need the "For" loop below
    For Each c In Range("D2:D10") 
        If c.Value = 12 Then
            MsgBox "Go to add to system"
            c.Value = 0
        End If
    Next c
End If

End Sub

edited Dec 12 '16 at 09:47

answered Dec 12 '16 at 08:46

Shai Rado

33,032
6
29
51

Hi, please can you help me replace range for specific cells ? or can I use both method together? thx – Fiínek Cahů Dec 14 '16 at 05:24
@FiínekCahů what is the specific cell? You can use a range also for a single cell. – Shai Rado Dec 14 '16 at 05:39
not range but specific cells such as "D29","D55" etc. – Fiínek Cahů Dec 15 '16 at 11:19
you can use range also for these, use `Range("D29,D55")` etc. – Shai Rado Dec 15 '16 at 11:22
I try to use similar code, please look to this page: [link] http://stackoverflow.com/questions/41163346/macro-range-specific-cells-msg-box [/link] – Fiínek Cahů Dec 15 '16 at 11:53

Macro with show messsage box if cell contains value

1 Answers1