Run Macro if Cell changes from Hot to Won

[Skybluekid]

Hi

I am running a change event macro. I have a column on the sheet, where the user changes a value from "Hot" to "Won". What I would like to happen is when this happens, a macro will run.

Thanks in Advance.

 

[JLGWhiz]

You can add the macro name to the existing Worksheet_Change macro after the line of code current execution completes and it will call that macro will run.
Sample

Private Sub Worksheet_Change(ByVal Target as Range)
'Current code here to do something
'add the called macro name here
'Current clean up code here, if any
End Sub

 

[Skybluekid]

Thank you for the reply, but I think you might have misunderstood what I am after.

I have uploaded a file: https://www.dropbox.com/s/u4g65n84291rhw0/Salesperson File.xlsm?dl=0

What I have. The sales person fills in his sheet. When the deal is won, he selects 100% and a macro will copy certain values to the Won Sheet, the same for Lost and Hot.

What I would like to happen is if a deal is marked as hot and the a deal is won, I would like do detect that change and then copy cels to the Won Sheet. The same for Hot to Lost.

Hope this is a bit clearer

 

[JLGWhiz]

You can have only one of a type event code per sheet, but more than one type event. If you want the code to run for different changes, then that has to be incorporated into one Change Event code. It is not the words that trigger the macro, but the event. So it would make no difference what the entry or removal in a cell is, the code still runs. It is what the code tells Excel to do based on the data or condition resulting from the change that will determine the results of the code execution. That said, I could not open your link to you worksheet so I cannot offer any code for your solution.

 

[Skybluekid]

After doing some hunting online, I have come up with this the below code:

Private Sub Worksheet_Calculate()




With Sheets("Dave Wilson").Cells(Target.Row, "X").Value

Application.EnableEvents = False
NewValue = .Value
Application.Undo
OldValue = .Value
.Value = NewValue
Application.EnableEvents = True

If OldValue = "Hot" And NewValue = "Won" Then

FValue = Range("E" & Target.Row).Value


Set Rng2 = Sheets("Hot Deals").Range("D" & Rows.Count).End(xlUp).Find(FValue)

If FValue Is Nothing Then

MsgBox "No Value was found on Hot Deals Sheet"

Else

Sheets("Hot Deals").Rng2.Rows.Delete

End If

Else: End If


End With


End Sub

 

[JLGWhiz]

Thanks for the feedback,
regards, JLG