CONDITIONAL FORMATTING & NEGATIVE VALUES

[sato]

I have a column D containing the names of some expenses and Income categories
and I want in column F to manually enter numbers which they will become
negative if the value in column D is an expense category or positive if the
value in column D is an Income Category

 

[Gord Dibben]

If you are manually entering numbers in a cell they cannot be formatted or
manipulated to be negative based upon a value in another cell.

You would need VBA for this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Column = 6 Then
On Error GoTo enditall
Application.EnableEvents = False
n = Target.Row
With Me.Range("D" & n)
If .Value <> "" _
And .Value = "expense" Then
With Me.Range("F" & n)
.Value = .Value * -1
End With
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub

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

Copy/paste to that module.


Gord Dibben MS Excel MVP