Update cell values after cell is changed
 

Hello, I need help with some code for IF then Else.

I am trying to look at amounts in cells b2, b3, b4 for a number of
certain days. When each one of these cells is changed, the amount in
the formula will be changed accordingly. I can only get this to work
if I use below 30 days...

I cannot use a private sub, since this will be used over and over
again in different workbooks.

Is there something wrong with my If statement??

Public Sub Renewal()
Dim Intx As Integer
Dim lngRow As Long
Dim lastrow As Long
Dim iCol As Long
Dim iRow As Long
Dim unit1 As Integer
Dim unit2 As Integer
Dim unit3 As Integer

Cells(1, 1).Select

unit1 = Range("$b$2").Value
unit2 = Range("$b$3").Value
unit3 = Range("$b$4").Value



With ActiveSheet
For iRow = 2 To lngRow
If InStr(1, .Cells(iRow, 2).Value, "appw1d",
vbTextCompare) Then
If LCase(.Cells(iRow, 1).Value) = LCase(" Target
Renewal") Then
For iCol = 6 To 20
'looks to see if row above formula cell is 0
If .Cells(iRow - 1, iCol).Value = 0 _
And IsEmpty(.Cells(iRow - 1, iCol)) = False Then
If .Cells(iRow, iCol - 1).Value = 0 _
And IsEmpty(.Cells(iRow, iCol - 1)) = False _
And unit1 + .Cells(iRow - 1, iCol).Value + _
.Cells(iRow - 1, iCol - 1).Value + .Cells(iRow - 1,
iCol + 2) < 90 _
And unit1 + .Cells(iRow - 1, iCol).Value +
..Cells(iRow - 1, iCol + 1) _
+ .Cells(iRow - 1, iCol + 2).Value 60 Then
.Cells(iRow, iCol + 2).Formula =
"=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-($b$2-60)/30)"

ElseIf .Cells(iRow, iCol - 1).Value = 0 _
And IsEmpty(.Cells(iRow, iCol - 1)) = False _
And unit1 + .Cells(iRow + 1, iCol).Value +
..Cells(iRow + 1, iCol + 1).Value < 60 _
And unit1 + .Cells(iRow + 1, iCol).Value +
..Cells(iRow + 1, iCol + 1).Value 30 Then
.Cells(iRow, iCol + 1).Formula =
"=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-($b$2-30)/30)"


This section works correctly

ElseIf .Cells(iRow, iCol - 1).Value = 0 _
And IsEmpty(.Cells(iRow, iCol - 1)) = False _
And unit1 + .Cells(iRow + 1, iCol).Value < 30 _
And unit1 + .Cells(iRow + 1, iCol).Value 0 Then
.Cells(iRow, iCol).Formula =
"=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-$b$2/30)"


ElseIf .Cells(iRow + 1, iCol).Value = 0 _
And IsEmpty(.Cells(iRow + 1, iCol)) = False Then
.Cells(iRow, iCol).Formula =
"=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)"
End If
End If
Next iCol
End If
End If
Next iRow

Thanks!