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

 

[TheCovenant75]

Thank you guys so much for the assistance. As i just woke up, i'm going to try the codes that were sent.

 

[TheCovenant75]

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

Thank you so much! This is exactly what i needed. Also as a new learner, where do you recommend I go for material to peruse to get better?

 

[TheCovenant75]

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

Thanks! I will keep this for reference in my Excel/VBA learning journey

 

[dreid1011]

Thank you so much! This is exactly what i needed. Also as a new learner, where do you recommend I go for material to peruse to get better?

I used a lot of google when I learned. Just look up what you want to achieve and you will likely find other forum posts about it or even videos. Worst case, you post back here with what you have managed to put together and we help straighten it out if it's not working the way you expect it to. It helps when you have a project to focus on. I started learning when I wanted to make a fancy userform powered work sheet to keep track of certain accounts at work.

I also learn as I go here helping others.

 

[TheCovenant75]

I used a lot of google when I learned. Just look up what you want to achieve and you will likely find other forum posts about it or even videos. Worst case, you post back here with what you have managed to put together and we help straighten it out if it's not working the way you expect it to. It helps when you have a project to focus on. I started learning when I wanted to make a fancy userform powered work sheet to keep track of certain accounts at work.

I also learn as I go here helping others.

Thats awesome! I'm currently working on a project which this is for. And its meant to show up a cash value for miles if its below the 250 threshold...so anything 250 miles and below it would show $250

Hmmm also, putting in the code.. well it works but when i update figures on my sheet it doesnt update... Should i have done an excel formula instead as Kevin9999 suggested? Except C12 isnt meant to go blank after it hits 251. I apologize if i was not clearer. >.<

Ok, C12 pulls data from another worksheet as stated and it looks something like this

C12 also has a formula that pulls data from said worksheet as well.

Now my request is that a formula that would trigger the 250 threshold if its equal or less and have C12 updated to reflect the $250

So A12 keeps its existing formula but a new one is combined with it to have C12 update to show $250 no matter what until 251 and above has been placed in the miles cell (A12) it does not go blank. It essentially resumes its first formula until the threshold has been passed again to activate the second formula. If that makes any sense

 

[kevin9999]

Did you try the code in post #9?

 

[TheCovenant75]

Did you try the code in post #9?

I did, however it fixates the values to 0 on both A12 and C12 and also wipes the formula from C12

 

[kevin9999]

That's the formula, I wanted to know if you'd tried the code?

 

[TheCovenant75]

That's the formula, I wanted to know if you'd tried the code?

Yes i've also tried the code and it doesn't update the figures, it does not update the sheet

A12 is below 250, however C12 has not been updated to reflect the change.

 

[kevin9999]

Did you put it in the worksheet code area of the sheet your image shows? Or did you put it in a standard module?
Here's a ;onk to the file I used to test the code - let me know if it works for you or not?
The Covenant.xlsm