Creating an automated macro that changes one cell value if another cell value is equal or less than a specific number

[TheCovenant75]

Hey, im new to vba coding and im trying to create an automated macro that constantly checks two cell values for my worksheet .
Basically i want it to check cell A12 and if its equal and or less than 250 to update Cell C12 with the number 250

I had tried to do it like this with what i learned from youtube videos (I'm sorry in advance if this hurts anyone here to read (>_<) )

Private Sub Worksheet_Change(ByVal Target As Range)

IF Sheets("Load Calculator").Cells(12, 1).Value => 250 then Sheets("Load Calculator").Cells(12, 3).Value = 250

End IF
End Sub

 

[dreid1011]

I believe you have to actually check the target value when you use event code like that for it to actually do anything. Are you wanting it to check when ANY cell changes or just A12? How does A12 update? User entry, vba, formula? And how often?

 

[TheCovenant75]

Only A12 has to update for C12 to update. So basically i have another worksheet that puts information into A12(has no formulas, its a blank cell thats fed a number), and i want a macro to target A12 when it has that value in it. If the value is equal to or below 250, it changes the value in C12 to always have 250 in it as well until A12 updates to something greater than 250 (251 and counting). My knowledge of vba is extremely green. Sigh

 

[dreid1011]

Only A12 has to update for C12 to update. So basically i have another worksheet that puts information into A12(has no formulas, its a blank cell thats fed a number), and i want a macro to target A12 when it has that value in it. If the value is equal to or below 250, it changes the value in C12 to always have 250 in it as well until A12 updates to something greater than 250 (251 and counting). My knowledge of vba is extremely green. Sigh

Okay, so that brings up the following question:

A12 is blank and C12 is 99.
The value 50 is entered into A12, and C12 is updated to 250.
The value 255 is entered into A12, what happens to C12?

 

[TheCovenant75]

Ok it works like this,

A12 is Blank without any information, C12 remains blank as well.
the value 250 and anything below 250 is put into A12 and C12 updates to 250 and stays that way with the only exception being 0 of course. (which would have 0 being 0 across both A12 and C12).
BUT if 251 and or anything above 250 is put into A12 then C12 doesn't do anything.

 

[dreid1011]

Ok it works like this,

A12 is Blank without any information, C12 remains blank as well.
the value 250 and anything below 250 is put into A12 and C12 updates to 250 and stays that way with the only exception being 0 of course. (which would have 0 being 0 across both A12 and C12).
BUT if 251 and or anything above 250 is put into A12 then C12 doesn't do anything.

So the first time C12 updates to 250, it doesn't ever change again?

 

[dreid1011]

If C12 only changes once, then this should do it, placed in the sheet module for the sheet A12/C12 is located on:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A12")) Is Nothing Then

If Sheets("Load Calculator").Cells(12, 1).Value <= 250 And Sheets("Load Calculator").Cells(12, 1).Value > 0 Then Sheets("Load Calculator").Cells(12, 3).Value = 250

End If

End Sub

 

[kevin9999]

BUT if 251 and or anything above 250 is put into A12 then C12 doesn't do anything.

Does that mean it retains whatever value was already in C12, or does it go blank, or what?

 

[kevin9999]

If C12 is meant to go blank if any value greater than 250 is entered into A12, then you could use a simple formula like this:

The Covenant.xlsm
	A	B	C
12	220		250
Sheet1

Cell Formulas
Range	Formula
C12	C12	=IF(A12="","",IF(A12=0,0,IF(AND(A12>0,A12<=250),250,"")))

On the other hand, if C12 should retain its existing value if a value greater than 250 is entered into A12, then I would recommend a Select Case method to cover all your desired scenarios. Right click the sheet tab name, select View Code, and copy the code below into the window that appears on the right of screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A12"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Select Case Target.Value
            Case ""
            Range("C12") = ""
        Case 0
            Range("C12") = 0
        Case Is > 0
            Select Case Target.Value
                Case Is <= 250
                Range("C12") = 250
            End Select
        End Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[bebo021999]

Your request has me puzzled. "If A12 changes, then C12 equals 250 and C12 maintains its value."

Could I have misunderstood your meaning?

Are you trying to say: "C12 = A12, and C12 should never be greater than 250."

If that's the case, you can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address (0,0) <> "A12" Or Target.Count > 1 Then Exit Sub
With Range("C12")
    . Formula = "=MIN(A12,250"
    .Value = .Value
End with
End Sub