Vba code when cell in range returns a certain value, enter formula in nearby cell

[dsrt16]

I know how to do this for one cell, but it needs to be with a range.

So if C12 says PayPal Fees, then E12 needs to return a formula. If C13 says PayPal Fees, then E13 needs to return a formula, etc.

If C12 says PayPal Fees, then the formula in E12 is =(E11*'-'Setup Page'!$Q$44)-'Setup Page'!$S$44
If C13 says PayPal Fees, then the formula in E13 is = (E12*'-'Setup Page'!$Q$44)-'Setup Page'!$S$44

I am running into problems for two reasons, the first reference in the formula always changes. If the formula is in row 12, the reference is E and the row above so 11. I don't know how to get it to change relative to the row the formula is in.

The other reason is I don't want to have to say if C12 changes then this; if C13 changes then this; etc. one at a time because it is a long range. C12:C190. So anytime just ONE cell in that range changes, then the E column in that row needs the formula.

Below was what I knew how to do. I know the code is wrong, but it worked in the sense that when I changed C12 to PayPal Fees, it did put the formula in E12. But in my code, the first formula reference is stagnant and as mentioned it needs to change. Then it, of course, tried to check all the other cells in the range, which were currently blank so it killed the page.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngInvoiceCategory As Range
Dim returnAmount As Range
Dim c1 As Range
Dim ws As Worksheet

Set ws = ActiveSheet


With ActiveSheet
      Set rngInvoiceCategory = ws.Range("C12:C190")
        Set returnAmount = ws.Range("E12:E190")

    


          For Each c1 In rngInvoiceCategory
             If c1.Value = Sheet1.Range("N44") Then
               ws.Range("E12").FormulaR1C1 = _  (Note: I can't really say range E12 because that changes. If C12 is the one saying PayPal Fees, then it is E12. If C13 is the one saying PayPal Fees, then it is E13 etc. 
        "=(R[-1]C*-'Setup Page'!R[32]C[12])-'Setup Page'!R[32]C[14]" (Note: I need that first reference to change based on the row it is in)'
                Else


          End If
            Next c1  (Note: I am sure a loop here is wrong since only one will change at a time). 
End With

End Sub

[6StringJazzer]

You do not seem to be using your With anywhere so remove it.

You do not need to refer to ActiveSheet or ws since this code is already associated with the sheet where the change will occur.

Using a loop is not wrong, but it's the wrong loop. Your loop goes through every cell in the range, not just the ones that changed. You don't need a loop if you know that only one cell at a time will change, but you really can't guarantee that--if someone does a paste to multiple cells, they will all change in one event.

This compiles but I can't test it without your file.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim rngInvoiceCategory As Range
   Dim returnAmount As Range
   Dim c1 As Range
   
   Set rngInvoiceCategory = Range("C12:C190")
   Set returnAmount = Range("E12:E190")
   
   For Each c1 In Target
   
      If Not Intersect(c1, rngInvoiceCategory) Is Nothing Then
      
         If c1.Value = Sheet1.Range("N44") Then
            Cells(c1.Row, "E").FormulaR1C1 = _
              "=(R[-1]C*-'Setup Page'!R[32]C[12])-'Setup Page'!R[32]C[14]" ' (Note: I need that first reference to change based on the row it is in)'
         End If
         
      End If
      
   Next c1 ' (Note: I am sure a loop here is wrong since only one will change at a time).

End Sub

[dsrt16]

Thanks. I am new to VBA. Cells(c1.Row, "E") makes a lot of sense. What does If Not Intersect mean?

Thanks. Your code worked. I just had to change the last references since they needed to be absolute: Setup Page'!R44C17)-'Setup Page'!R44C19"

[6StringJazzer]

What does If Not Intersect mean?

Intersect is a function that returns a Range object that is the intersection of all the ranges listed as arguments to it. In this case it will give you the intersection between cell c1 and Range rngInvoiceCategory. If c1 is in rngInvoiceCategory, then their intersection is c1. If c1 is not in rngInvoiceCategory, then the result is Nothing. Nothing is a special value for an object that indicates that the object is not set to reference anything (this would require a longer explanation about how objects work). For our purposes here, if the function returns Nothing, it means there is no intersection among the ranges listed. Putting a Not in front means the opposite. So in this particular case,

Not Intersect(c1, rngInvoiceCategory) Is Nothing

means "c1 is within range rngInvoiceCategory"