Cell After Update function (Excel 2003)

Topic

New Reply

Hi

I have a table with columns for (amongst other things) amount and tax plus a column with a drop down list with options No (default) and Yes. I’d like to be able to run some code so that if a user selects Yes the amount is reduced by tax and tax is entered in its column. I guess I also need code to reverse this if the user changes the cell back to No.

Can anyone point me in the right direction?

David

Reply | Quote

Replies

Why do you need a macro? A formula with an IF would seem to fit the need.

Could you elaborate on your setup and what you need exactly?

Steve

Reply | Quote

Thanks for the prompt response.

Let us say that cell B1 is blank and the user enters a figure of

Reply | Quote

Right-click the sheet tab and select View Code.
Enter or paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Const dblVat = 0.175
Application.EnableEvents = False
If Not Intersect(Range(“B1:B20”), Target) Is Nothing Then
For Each oCell In Intersect(Range(“B1:B20”), Target).Cells
If IsNumeric(oCell) Then
If oCell.Offset(0, 1) = “Yes” Then
oCell.Offset(0, 2) = oCell * dblVat / (1 + dblVat)
oCell = oCell / (1 + dblVat)
End If
End If
Next oCell
End If
If Not Intersect(Range(“C1:C20”), Target) Is Nothing Then
For Each oCell In Intersect(Range(“C1:C20”), Target).Cells
If IsNumeric(oCell.Offset(0, -1)) Then
If oCell.Offset = “Yes” Then
oCell.Offset(0, 1) = oCell.Offset(0, -1) * dblVat / (1 + dblVat)
oCell.Offset(0, -1) = oCell.Offset(0, -1) / (1 + dblVat)
Else
oCell.Offset(0, -1) = oCell.Offset(0, -1) + oCell.Offset(0, 1)
oCell.Offset(0, 1).ClearContents
End If
End If
Next oCell
End If
Application.EnableEvents = True
End Sub

Adjust the ranges as needed. See attached sample workbook.

Reply | Quote

I am once again filled with admiration. Exactly what I was looking for. It will take me a little while to get it into my existing spreadsheet but the sample proves it works.

I’d love to know what you do for a living. Man, you’re always there within minutes with awesome responses.

Regards
David

Reply | Quote

How is tax calculated? Or how does “and tax is entered in its column” work?

Reply | Quote