Change cell value and macro runs

[Macdave_19]

Hi folks i'm having trouble getting this code to work!! i would like certain cell values to change when i update the range B4 but i don't know quite how to set the range as b4?

any help is much appreciated, here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Range("B4")
    Dim SAgent As String
    SAgent = Range("B4").Value
    
        If Target.Value <> SAgent Then
            Range("E4,E24,H4,H13,H23,H33,K4,K18,K30").Value = SAgent
        End If
End Sub

Thanks for taking the time to view my thread everyone and for any help

[StephenR]

Your syntax isn't quite right, but I don't follow. You appear to be setting SAgent to equal Target (B4) but the second If only works if Target doesn't equal SAgent which by definition cannot happen?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SAgent As String
    If Target = Range("B4") then
    SAgent = Range("B4").Value
   
        If Target.Value <> SAgent Then
            Range("E4,E24,H4,H13,H23,H33,K4,K18,K30").Value = SAgent
        End If
end if
End Sub

[Macdave_19]

OK so lose the contradiction in there and im still left with:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SAgent As String
    If Target = Range("B4") then
    SAgent = Range("B4").Value
        
    Range("E4,E24,H4,H13,H23,H33,K4,K18,K30").Value = SAgent
       
End If
End Sub

[BigBas]

Are you asking how to make a change if the target is cell B4?

If so, try this code:

If Target.Address = "$B$4"

Otherwise, I'm not sure what you are asking.

[stevekirk]

hi Macdave_19,

is the idea of this macro to change all the cells in the range to what is in b4 ?

steve

[Macdave_19]

Yes steve it is mate!!

[stevekirk]

hi

are you having problems with the code you last posted ?

the reason i ask is because i wanted something like this and it works for me

steve