VBA - If Cell value is changed

[hmltnangel]

Hi All,

Kind of a follow on from a query I had a few weeks back. But an additional piece is needed to the code now, and I keep getting it the wrong way around. So hopefully someone can offer the right method for me.

This is the code.

If Target.Value > 0 Then
            If Target.DisplayFormat.Interior.Color = 8696052 Then
            MsgBox "Blah Blah Blah"
            End If
End If

The initial part of the code, if the cell colour was orange works great. It pops up the message box as required. However the change thats needed now is to only have the pop up box if the value is changed, and the colour is orange. I did try adding a little bit to the front of the code, but its not working if the value is changed, it only works if the value is greater than 0 and Orange.

thanks

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value > 0 Then
        If Target.Interior.Color = 8696052 Then
            MsgBox "Blah Blah Blah"
        End If
    End If
End Sub

 

[hmltnangel]

Sorry mumps, I didnt include the full code. The DisplayFormat piece is to allow for Conditional Format of the cell.

Basically, if the cell value gets changed, and the colour is orange, then Msg Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
If Target.Value > 0 Then
            If Target.DisplayFormat.Interior.Color = 8696052 Then
            MsgBox "Blah Blah Blah"
            End If
End If

End Sub

 

[mumps]

This works for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value > 0 Then
        If Target.DisplayFormat.Interior.Color = 8696052 Then
            MsgBox "Blah Blah Blah"
        End If
    End If
End Sub

 

[hmltnangel]

For me, it works as follows:

If you change the value, and its orange, I get no msg box.

If you change the value, and its orange, then reselect the cell you get a msg box.

Can it work if you change the value, and its orange, then you get the msg box? (without having to click off the cell, and reselect it?)

 

[Joe4]

For me, it works as follows:

If you change the value, and its orange, I get no msg box.

If you change the value, and its orange, then reselect the cell you get a msg box.

Can it work if you change the value, and its orange, then you get the msg box? (without having to click off the cell, and reselect it?)

That behavior you are describing is when you use the "Worksheet_SelectionChange" event.
Look at mumps last code. He is using "Worksheet_Change", not "Worksheet_SelectionChange".
Did you make that change to your code?

 

[NoSparks]

With Worksheet_Change the target is the cell you're coming out of
With Worksheet_SelectionChange the target is the cell you're going into

 

[Joe4]

Quite simply, the "Worksheet_Change" event procedure fires whenever a cell's value is changed (either manually or by code, not by a formula or link).
So the "Target" cell in this case is the cell being changed.

The "Worksheet_SelectionChange" event procedure fires whenever a cell is selected.
The "Target" cell in this case is the cell being selected.

 

[hmltnangel]

Sorry I missed that Mumps. Joe, thanks for the pointing it out. Such a subtle change I completely missed it.

You two are brilliant. Thanks

 

[Joe4]

Sorry I missed that Mumps. Joe, thanks for the pointing it out. Such a subtle change I completely missed it.

You two are brilliant. Thanks

You are welcome.
Glad we were able to help!

Yes, a subtle change, but one that changes how it works entirely!