Conditional Data Validation

[evoxfan]

I have a column (D) to enter a dollar amount of a transaction. All
transactions are classifed in column A as either cost or revenue. I want to
make sure that cells classified as cost in column A have a negative value and
cells classified as revenue in column A have a positive value.

Any help is appreciated.

Thanks.

 

[JP]

You could do it with Conditional Formatting. Select D1 and go to
Format > Conditional Formatting.

Condition 1:
=AND(A1="Cost",D1>0)

Condition 2:
=AND(A1="Revenue",D1<0)

Make the pattern "Red" for both, that way if negative numbers are
entered next to "Revenue", or positive numbers next to "Cost", they
will highlighted in red. Fill down the cells as needed to copy the
conditional formatting all the way down to the end of your data.

--JP

 

[Gord Dibben]

So as not to annoy users with messages use event code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Offset(0, -3).Value = "cost" Then
On Error GoTo endit
Application.EnableEvents = False
Target.Value = Target.Value * -1
End If
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.

Alt + q to return to the Excel window.

Enter all numbers in column D as positive and those with "cost" in column A
will turn negative.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

I would suggest this revised edition if you want to add more "cost" and
"revenue" cells to column A

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
Application.EnableEvents = False
If Target.Column = 4 And Target.Offset(0, -3).Value = "cost" Then
Target.Value = Target.Value * -1
End If
endit:
Application.EnableEvents = True
End Sub


Gord