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]

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

I put it in the worksheet code area, also tested the file you sent, it works fine with the values being raw. However when i used formulas in the cell eg. A1*B2 and had it multiply a value which would equal 200 Hypothetically triggering the C12 cell to output 250 formula or not. That did not work

 

[kevin9999]

So there's a formula in A12? If that's the case, a worksheet change event won't work - it needs to be a worksheet calculate event.
Try this instead:

Private Sub Worksheet_Calculate()
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim c As Range: Set c = Range("A12")
        Select Case c.Value
            Case ""
            Range("C12") = ""
        Case 0
            Range("C12") = 0
        Case Is > 0
            Select Case c.Value
                Case Is <= 250
                Range("C12") = 250
            End Select
        End Select
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[kevin9999]

I've updated the 'test' file:
The Covenant.xlsm

 

[TheCovenant75]

So there's a formula in A12? If that's the case, a worksheet change event won't work - it needs to be a worksheet calculate event.
Try this instead:

Private Sub Worksheet_Calculate()
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim c As Range: Set c = Range("A12")
        Select Case c.Value
            Case ""
            Range("C12") = ""
        Case 0
            Range("C12") = 0
        Case Is > 0
            Select Case c.Value
                Case Is <= 250
                Range("C12") = 250
            End Select
        End Select
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Alrighty, so its getting better. By the way i really appreciate your assistance on this. I'm as dumb as a blonde from white chicks when it comes to VBA or anything excel. Is there a way to update C12 without overriding the formula in there as well? The formula that is in C12 is A12*B12

 

[kevin9999]

Is there a way to update C12 without overriding the formula in there as well?

Not by using the suggested code - but why would you want to if the code gives you the result you're looking for?

 

[TheCovenant75]

Not by using the suggested code - but why would you want to if the code gives you the result you're looking for?

its because of the way the cell pulls information prior to the threshold. it calculates data from A12 and another cell B12 to get a dollar value.

 

[kevin9999]

I'll put it another way. What is the code doing that it shouldn't do, and/or what is the code not doing that it should do?

 

[TheCovenant75]

I'll put it another way. What is the code doing that it shouldn't do, and/or what is the code not doing that it should do?

It's removing the formula in C12 and putting 250 as the raw number. That's what it shouldn't do. What it should do is; keep the formula in C12 and update with 250 so if the number in A12 does change, the formula in A12 can resume as normal until a number equal or lower than 250 is inputted again. The code is fine, however I just want it to keep any existing formulas that are in the cells and not have them override or if thats not possible, have it put the formula back if it increases to 251 and above

 

[kevin9999]

What it should do is; keep the formula in C12 and update with 250

What do you mean by "update with 250"? A cell can contain either a formula or a constant - it can't contain both.

so if the number in A12 does change, the formula in A12 can resume

As above - what is actually in A12, a number or a formula, it can't be both.

I just want it to keep any existing formulas that are in the cells and not have them override

Could you provide a copy of your sheet using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform so I can see what formulas you are trying to preserve.
The real difficulty in providing you with an acceptable solution is your previously stated requirement that - if the value in A12 exceeds 250 then the value in C12 should remain what it was. The reality is that, if the value in C12 is derived by a formula that points to A12, any a change in A12 will result in a change in C12. Formulas have no 'memory' of what value they contained before a recalculation - you need code for that. But I'm struggling to follow your requirements in post #28 insofar as converting them to a workable code solution.
Let's get back to basics (forget formulas for now). What value do you want to appear in cell C12 when - after a calculation of the formula in A12, the value in A12 becomes:
1. A blank?
2. Zero?
3. Greater than 0 and equal to or less than 250?
4. Greater than 250?

 

[TheCovenant75]

Load Calculator ver 3.5.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Email Address :							BROKER EMAILS PLACED HERE
4
5	SELECT A LOAD BOARD:		INSERT REFERENCE NUMBER:			INSERT LOAD REFERENCE NUMBER HERE
6
7	DEADHEAD TO PICK UP :	Virginia Beach, VA
8
9	PICK UP LOCATION :								DROP OFF LOCATION :
10
11	MILES TO DESTINATION	RATE PER MILE	OUR PROPOSED RATE			COUNTER OFFER			DISCOUNTED RATE PER MILE			DISCOUNTED RATE
12	#VALUE!	1.5	#VALUE!						0.85			#VALUE!
LOAD CALCULATOR

Cell Formulas
Range	Formula
A12	A12	='DATA '!C13
C12	C12	=A12*B12
L12	L12	=A12*I12

Cells with Data Validation
Cell	Allow	Criteria
B5	List	='DATA '!$B$30:$B$34

Here is the Mini Sheet: The cells in question are A12 and C12 which have formulas in them. The way it works is you put in A location for pick up and drop off and it gives you a mileage and price based on the mileage. What i want done is if there is an instance where the location provides mileage of 250 and below it comes back with the price as 250 and then if its above it progresses as normal.