Need edit on this Macro

[Shinod]

Hi Guys,

I want to run a Macro if I change the value in "H3" (Which is working) but along with that, I want to activate the value in "H3" with "A2" if I change the value in "A2"

Could you correct it for me, please?

Thank you in Advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$3" Then
        Call Hide_Unused_Rows
    End If
        If Target.Address = "$A$2" Then
        Range("H3") = Range("A2").Value
    End If
End Sub

 

[Siddharth Rout]

Important Tips:

1. Whenever you are working with Worksheet_Change event, always switch Off events if you are writing data to any cell. This is required so that the code doesn't retrigger the Change event, and go into a possible endless loop
2. Whenever you are switching off events, use error handling to turn it back on, else if you get an error, the code will not run the next time.

Is this what you are trying (UNTESTED)?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
   
    Application.EnableEvents = False
   
    If Not Intersect(Target, Range("H3")) Is Nothing Then Hide_Unused_Rows
   
    If Not Intersect(Target, Range("A2")) Is Nothing Then Range("H3").Value = Range("A2").Value
   
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

 

[Shinod]

Yes, Exactly. Thank you for your help @Siddharth Rout

 

[Siddharth Rout]

Yes, Exactly. Thank you for your help @Siddharth Rout

Awesome! Thanks for letting us know it worked

 

[Shinod]

@Siddharth Rout , There is an issue:

Issue is I need to run the macro if i change A2 also.

ie i need a way like if i change A2 Run Macro & Activate the value with A

 

[Siddharth Rout]

@Siddharth Rout , There is an issue:

Issue is I need to run the macro if i change A2 also.

ie i need a way like if i change A2 Run Macro & Activate the value with A

You mean like this?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
   
    Application.EnableEvents = False
   
    If Not Intersect(Target, Range("H3")) Is Nothing Then Hide_Unused_Rows
   
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Hide_Unused_Rows
        Range("H3").Value = Range("A2").Value
    End If
    
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

 

[Shinod]

No. The first part is okay. the macro is running if i change value in H3.

Then next if I change I want still need to run the macro AND activate the value in H3 with A2.

 

[Siddharth Rout]

No. The first part is okay. the macro is running if i change value in H3.

Then next if I change I want still need to run the macro AND activate the value in H3 with A2.

Not sure if i understand. In the code above...

1. If you change the value of H3, the Hide_Unused_Rows macro is run.
2. If you change the value of A2, the Hide_Unused_Rows macro is run and then H3 gets the value from A2

If this is not what you want then you may have to explain it a bit more...

 

[Shinod]

Not sure if i understand. In the code above...

1. If you change the value of H3, the Hide_Unused_Rows macro is run.
2. If you change the value of A2, the Hide_Unused_Rows macro is run and then H3 gets the value from A2

If this is not what you want then you may have to explain it a bit more...

Yes. This is what i want

 

[Siddharth Rout]

Yes. This is what i want

But that is what the code is already doing. Is it not?