Msg Box appears when "Yes" is entered into a cell in a range

[camjamco]

When someone enters "Yes" into any cell in Column C I have it set to conditional format in red to highlight it however I would also like a text box to appear saying "You have identified this as a risk, please enter more details into column X".

I have tried a few different macros on different forums but I cant get it to work

 

[steve the fish]

Hi. See if this works. Right click on sheet tab, press view code and paste in the below:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, c As Range

Set rng = Intersect(Target, Columns("C"), ActiveSheet.UsedRange)
If Not rng Is Nothing Then
    For Each c In rng
        If LCase(c) = "yes" Then
            MsgBox "You have identified this as a risk, please enter more details into column X"
            Exit For
        End If
    Next
End If
    
End Sub

 

[camjamco]

That didnt work, when I type in yes in C19 the conditional formatting runs but not the message box.

I have a little exposure to macros but not a lot so just to clarify after clicking view code I am copying it straight into the sheet rather ThisWorkbook

 

[steve the fish]

Yes you put it into the sheet module.

 

[camjamco]

Thank you, that is all working!

I was adding it in the wrong area aha

 

[camjamco]

Just one quick last question,

In column F I have a name, for the message is it possible to have the message as

"You have identified "Name" as a risk, please enter more details into column X"

 

[steve the fish]

You can just add another test:

Set rng = Intersect(Target, Columns("C"), ActiveSheet.UsedRange)
If Not rng Is Nothing Then
    For Each c In rng
        If LCase(c) = "yes" Then
            If Len(c.Offset(0, 3)) > 0 Then
                MsgBox "You have identified " & c.Offset(0, 3) & " as a risk, please enter more details into column X"
            Else
                MsgBox "You have identified this as a risk, please enter more details into column X"
            End If
            Exit For
        End If
    Next
End If

 

[camjamco]

Im not sure what I have now done as I tried using that and it didnt work so tried to revert it back to just the original code and now that isnt working either

Sorry!

 

[steve the fish]

I dont know what isnt working means. What is happening? Heres the instructions again. Go to the sheet where you want the macro to run. Right click on the sheet tab. Press view code. Paste in this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, c As Range

Set rng = Intersect(Target, Columns("C"), ActiveSheet.UsedRange)
If Not rng Is Nothing Then
    For Each c In rng
        If LCase(c) = "yes" Then
            If Len(c.Offset(0, 3)) > 0 Then
                MsgBox "You have identified " & c.Offset(0, 3) & " as a risk, please enter more details into column X"
            Else
                MsgBox "You have identified this as a risk, please enter more details into column X"
            End If
            Exit For
        End If
    Next
End If

End Sub

Close the window. In the sheet enter yes into a cell in column C. Provided there is something in column F of the same row you will get the new message box provided macros are enabled within the workbook.

 

[camjamco]

Thank you for all your help on this one!