Conditional formatting & negative cell value

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,738
Office Version
  1. 2007
Platform
  1. Windows
Morning,

I have conditional formatting where if i type REFUND in a cell of which is in column B then that row in question the font color is RED.
This works fine.

What i now need to sort out is when i type a value in a cell in column D the value must change / show a negative value.
I am not to good at explaining these things but if i enter £10.00 it is classed as a + amount.
I wish to enter £10.00 then when i leave the cell it is shown as -£10.00

If you type -£10.00 all is good BUT some staff forget & just type £10.00 thus making it a positive value & not a negative value.

How would this be achieved please.

Something like if a cell in column B is shown as REFUND then make the cell in column D of that row a -£10.00 value.

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Re: Conditional formatting & negative cell value help

It could be done with Data Validation but I think it is better with VBA :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [D:D])
If Intersect(Target, [D:D]) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In rng
    If cel(1, -1) = "REFUND" And IsNumeric(cel) And cel > 0 Then cel = cel * -1
Next
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Re: Conditional formatting & negative cell value help

You would need VBA to do what you want.

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in there.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Application.EnableEvents = False
            Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
            Application.EnableEvents = True
        End If
    End If

End Sub
This should automatically make the amount in column D negative whenever "REFUND" is in column B.
It runs automatically anytime a single cell in columns B or D is manually updated.
 
Last edited:
Upvote 0
Re: Conditional formatting & negative cell value help

Improved version of my previous code :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, cel As Range
Set rng1 = Intersect(Target, [D:D])
Set rng2 = Intersect(Target, [B:B])
Application.EnableEvents = False
If Not rng1 Is Nothing Then
    For Each cel In rng1
        If cel(1, -1) = "REFUND" And IsNumeric(cel) And cel > 0 Then cel = cel * -1
    Next
End If
If Not rng2 Is Nothing Then
    For Each cel In rng2.Offset(, 2)
        If cel(1, -1) = "REFUND" And IsNumeric(cel) And cel > 0 Then cel = cel * -1
    Next
End If
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
Re: Conditional formatting & negative cell value help

footoo,

You need to remove the "x" in front of xWorksheet_Change, or else you code will never trigger!
Code:
Private Sub [COLOR=#ff0000]xWorksheet[/COLOR]_Change(ByVal Target As Range)
 
Upvote 0
Re: Conditional formatting & negative cell value help

footoo,

You need to remove the "x" in front of xWorksheet_Change, or else you code will never trigger!
Code:
Private Sub [COLOR=#ff0000]xWorksheet[/COLOR]_Change(ByVal Target As Range)
Done. Thanks.
 
Upvote 0
Re: Conditional formatting & negative cell value help

Joe4 thats great,can we just add to it if possible.

I you delete REFUND the value is still shown in the cell.
Once REFUND is deleted can the value also be deleted at the same time.

Many thanks.
With my learning issues i find your replied spot on as you advise exactly how /where to assign / place the code & i thanks you for that.

I am using your code shown below.

Code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Application.EnableEvents = False
            Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
            Application.EnableEvents = True
        End If
    End If
 [COLOR=#333333]End Sub[/COLOR]
 
Last edited:
Upvote 0
Re: Conditional formatting & negative cell value help

I you delete REFUND the value is still shown in the cell.
Once REFUND is deleted can the value also be deleted at the same time.
This can get a little tricky. If you we just look at column B, and see there is nothing in there, we could blank column D.
But is this REALLY want you want to happen? That means whenever column B is blank, we will make column D blank.
Is there ever an instance when you want a value in column D when column B is blank?

If you only want column D to go blank when column B goes from "REFUND" to blank, that is a bit more complex, as we will need to capture the value of column B before it changes.
 
Upvote 0
Re: Conditional formatting & negative cell value help

I you delete REFUND the value is still shown in the cell.
Once REFUND is deleted can the value also be deleted at the same time.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, cel As Range
Set rng1 = Intersect(Target, [D:D])
Set rng2 = Intersect(Target, [B:B])
Application.EnableEvents = False
If Not rng1 Is Nothing Then
    For Each cel In rng1
        If cel(1, -1) = "REFUND" And IsNumeric(cel) And cel > 0 Then cel = cel * -1
    Next
End If
If Not rng2 Is Nothing Then
    For Each cel In rng2.Offset(, 2)
        If cel(1, -1) = "REFUND" And IsNumeric(cel) And cel > 0 Then
            cel = cel * -1
         ElseIf cel(1, -1) = "" Then
            cel.ClearContents
         End If
    Next
End If
Application.EnableEvents = True
End Sub
 
Upvote 0
Re: Conditional formatting & negative cell value help

If you want column D blank anytime column B is blank, this would do it:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "D").ClearContents
        End If
        Application.EnableEvents = True
    End If

End Sub
If you want column D blank ONLY if column B changes from "REFUND" to blank, use this:
Code:
Dim prevVal As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Capture value of column B before change
    If Target.Column = 2 Then prevVal = UCase(Target)
    
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        Application.EnableEvents = False
        Select Case UCase(Cells(Target.Row, "B"))
            Case "REFUND"
                Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
            Case ""
                If prevVal = "REFUND" Then Cells(Target.Row, "D").ClearContents
        End Select
        Application.EnableEvents = True
    End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top