Worksheet Change Code

**Gos-C** · 10-02-2011, 09:41 AM

Hi all,

I want to use the code below to analyze a spreadsheet and calculat the amounts still outstanding from column R. I have added two columns, AA and AB. If the amount in column R is still outstanding, the user enters N (for not collected) in column AA and the outstanding amount from column R would be copied to column AB. When the spreadsheet is analyzed at a later date and the outstanding amount was collected, the user deletes the N in column AA and the amount in column AB should be deleted also. However, my code is not deleting the amount. Any help?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRange As Range
Set cRange = Range("AA5:AA" & Cells(Rows.Count, 1).End(xlUp).Row)
If Target.Cells.Count > 1 _
Or Intersect(Target, Range("AA5:AA" & Cells(Rows.Count, 1).End(xlUp).Row)) Is Nothing Then Exit Sub
        If UCase(Cells(Target.Row, "AA")) = "N" Then

                Cells(Target.Row, "AB") = Cells(Target.Row, "R")

        If Cells(Target.Row, "AA") = vbNullString Then
                Cells(Target.Row, "AB") = vbNullString

        End If
        End If

End Sub

Thank you,
Gos-C

**quekbc** · 10-02-2011, 09:56 AM

It appears that your second IF statement is nested within the first IF statement. This means that the second IF statement will never occur because if the target cell is not "N", the second IF statement will not be run when it should.

Try amending the code to be

If UCase(Cells(Target.Row, "AA")) = "N" ThenCells(Target.Row, "AB") = Cells(Target.Row, "R")
ElseIf Cells(Target.Row, "AA") = vbNullString ThenCells(Target.Row, "AB") = vbNullStringEnd If

Hope this helps.