when a value manually changes in a cell, another cell should pick a value based on conditions

[leejoos]

Hi There,

I have a riddle to solve! In a sheet, I need to get changed the data in cell G27 whenever I change a value in F27 based on the conditions specified. The conditions are, if the value in F27 is >10, then G27 should show [TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl63, width: 108"]±0.2 , if F27 is in between 6 - 10, then [TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]±0.15, if F27 is in between 3 -6, then [TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]±0.10 and if F27 id <3, then [TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]±0.05. Can someone help me to solve this with VBA? I am just a newbie in VBA!

Best Regards,

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[vcoolio]

Hello Liju,

If this is to work on the same sheet as per your previous thread, then change the previous code to the following:-

Private Sub Worksheet_Change(ByVal Target As Range)

Call ChangeEvent1(Target)
Call ChangeEvent2(Target)

End Sub

Private Sub ChangeEvent1(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("F29")) Is Nothing Then Exit Sub
Range("F4:F5").Value = Range("F31:F32").Value

End Sub

Private Sub ChangeEvent2(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("F27")) Is Nothing Then Exit Sub

If Range("F27").Value > 10 Then Range("G27").Value = "±0.20"
If Range("F27").Value > 6 And Range("F27") < 10 Then Range("G27").Value = "±0.15"
If Range("F27").Value > 3 And Range("F27") < 6 Then Range("G27").Value = "±0.10"
If Range("F27").Value < 3 Then Range("G27").Value = "±0.05"

End Sub

This should take care of both events for you. The Worksheet_Change event should now call either of the event changes independently.

I hope that this helps.

Cheerio,
vcoolio.