Need to clear the contents in F4 & F5 when the value is changed in cell F29.

[leejoos]

Hello there, I am quite new to VBA. I have an issue to be resolved. I need a code to clear the contents in F4 & F5 when ever the value in F 29 is changed manually. And also new values from F31 & F32 needs to be copied to F4 & F5 everytime it happens. Kindly help on it please!

Regards,
Liju

 

[Zubair Muhammad]

Hi,

You can use the following code. But insert it in the Worksheet Module. Right click the sheet tab>>>View Code>>>Paste the code below in the VBA window


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set TriggerCell = Range("F29")

If Not Application.Intersect(TriggerCell, Target) Is Nothing Then
Range("F4:f5").ClearContents
Range("F31:F32").Copy Range("F4:F5")
End If
End Sub

 

[vcoolio]

Hello Liju,

Try the following code in the worksheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F29")) Is Nothing Then Exit Sub

Range("F4:F5").Value = Range("F31:F32").Value

End Sub

To implement the code:_

- Right click on the sheet tab where your data is.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[leejoos]

Dear Cheerio,

Thanks a lot for your great support! It really worked well

Regards

Hello Liju,

Try the following code in the worksheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F29")) Is Nothing Then Exit Sub

Range("F4:F5").Value = Range("F31:F32").Value

End Sub

To implement the code:_

- Right click on the sheet tab where your data is.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[leejoos]

Dear Mr.Zubair,

Thanks a lot for your quick response to my query and that indeed was a great help to resolve my issue though the I had to change a little in the code to paste the value only in F4 & F5.

Regards
Liju

Hi,

You can use the following code. But insert it in the Worksheet Module. Right click the sheet tab>>>View Code>>>Paste the code below in the VBA window


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set TriggerCell = Range("F29")

If Not Application.Intersect(TriggerCell, Target) Is Nothing Then
Range("F4:f5").ClearContents
Range("F31:F32").Copy Range("F4:F5")
End If
End Sub

 

[vcoolio]

Hello Liju,

You're welcome. I am glad that I was able to help,

Cheerio,
vcoolio,