I want to set a value for a cell [S12] in a sheet called "AlphaPackage_2017" based on the other cell's value [S3] .
checking should follow the value in cell [S3] for any changing value and update immediately the value in cell [S12].
I wrote the below code:
Do
If S3.Value > 6 Then
S12 = 20
Else
S12 = 7
End If
Loop
the code does not work correctly, any advise is appreciated ... and also where the code should be write: in Module or in Page?
In the code module of the AlphaPackage_2017 worksheet, write this routine:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "S3" Then
Me.Range("S12").Value = IIf(Target.Value > 6, 20, 7)
End If
End Sub
Intersect which is more accurate a solution :)
Adress should be Address). Seize the occasion and start using Option Explicit, the compiler will then spot for you such mistakes immediately :)Cell Link property of the control and observe what happens. When facing a specific problem it should be worth a dedicated question :)Add the code below in the Worksheet_Change event of "AlphaPackage_2017" sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
' run the code below only if a value in cell "S3" is changed
If Not Intersect(Target, Range("S3")) Is Nothing Then
Select Case Target.Value ' <-- check the value of Range("S3")
Case Is > 6
Range("S12").Value = 20
Case Else
Range("S12").Value = 7
End Select
End If
End Sub
Follow screen-shot below where to add this code (just in case you haven't used a Worksheet event before):
