Execute macro on cell value change

Hi guys I need your help once again!

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = ActiveSheet.Range("K3:K350")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
            MsgBox "I'm In"
            MsgBox ActiveCell.Column
            MsgBox ActiveCell.Row
        If ActiveCell.Value < 0.001 Then
            MsgBox "I'm In Again"
            ActiveCell.NumberFormat = "0.0E+00"
        Else
            ActiveCell.NumberFormat = "0"
        End If
       
    End If
End Sub

Its basically just to change the formatting of a cell when the value is entered so that it shows up if the value is very small (the value of the cell can range from 10^6 to 10^-9).

However when you change the value of the cell, the macro runs off the cell that you selected afterwards (i.e. if you click off the cell then it takes the value of the cell you selected next, or if you press carriage return to leave that cell then it takes the value from the cell below) what is the way to stop it doing this? I tried surrounding my code in an

Application.EnableEvents

code but that seemed to make no difference?

Any help is greatly appreciated

Regards

Tom

Hi Tom,

I couldn't get it to work by finding the last active cell.

But I do have an alternative that should do the trick, using a loop to format every cell within the range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
Dim KeyCells As Range
Set KeyCells = ActiveSheet.Range("K3:K350")
    
Dim SR As Long
SR = 3
   
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Do
If Range("K" & SR).Value < 0.001 Then
Range("K" & SR).NumberFormat = "0.0E+00"
Else
Range("K" & SR).NumberFormat = "0"
End If
SR = SR + 1
Loop Until SR = 351
End If
    
End Sub

Let me know if this works for you!

Owen

That worked a treat! Thanks mate