Conditional formatting & negative cell value

[ipbr21054]

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

 

[footoo]

Re: Conditional formatting & negative cell value help

It could be done with Data Validation but I think it is better with VBA :

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

 

[Joe4]

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.

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.

 

[footoo]

Re: Conditional formatting & negative cell value help

Improved version of my previous 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

 

[Joe4]

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!

Private Sub [COLOR=#ff0000]xWorksheet[/COLOR]_Change(ByVal Target As Range)

 

[footoo]

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!

Private Sub [COLOR=#ff0000]xWorksheet[/COLOR]_Change(ByVal Target As Range)

Done. Thanks.

 

[ipbr21054]

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.

[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]

 

[Joe4]

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.

 

[footoo]

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.

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

 

[Joe4]

Re: Conditional formatting & negative cell value help

If you want column D blank anytime column B is blank, this would do it:

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:

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