Need VBA Help to change value in one cell, based on the data in another.....

[SecFire]

Good Morning,

I have a spreadsheet I am working on to maximize my time and organize my workflow. Covid has definitely changed the way I have to do things.
I have set the sheet up so that i am using data validation to select key fields from a dropdown. I would like to automatically have the value of the
probability cell change, based on the value of the field in the Status cell.
For example,
if Status in J7 = Target, then Probability should = 10% in U7.

Could anyone assist me with the code I would need to create?

 

[mumps]

Format column J as 'Percentage'. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Opportunities sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column J.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column = 10 Then
        Select Case Target.Value
            Case "Target"
                Range("U" & Target.Row) = 0.2
            Case "Qualify"
                Range("U" & Target.Row) = 0.1
            Case "Discover"
                Range("U" & Target.Row) = 0.3
            Case "Verify"
                Range("U" & Target.Row) = 0.5
            Case "Present"
                Range("U" & Target.Row) = 0.9
            Case "Close"
                Range("U" & Target.Row) = 0.95
            Case "Closed Won"
                Range("U" & Target.Row) = 1
            Case "Closed Lost"
                Range("U" & Target.Row) = 0
            Case "Closed Abandoned"
                Range("U" & Target.Row) = 0
        End Select
    End If
End Sub

Your sheet contains merged cells. You should avoid merging cells because they almost always cause problems for macros. You could simply widen a column if you need more space or do a little research into "CenterAcrossSelection". This has the same effect as merging without actually merging any cells. Fortunately, in your case the macro works with no problems but this is not usually the case with merged cells.