vba Code shows wrong format in cell & autosum also ignors the value

[ipbr21054]

Morning,

Partial code supplied below.
If in column B the user types REFUND the now & then as opposed to seeing -£10.00 in column D i see (-£10.00) & its not taken into consideration on my autosum

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
    If Not (Application.Intersect(Target, Range("A3:K28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If
    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("B5:L" & Rows.Count))
    
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each cell In rng
        cell = UCase(cell)
    Next cell
    Application.EnableEvents = True

 

[RobP]

I believe its down to the "formatting" of the cells in which you have your values (eg. col D ?). In accounting, having the figure in brackets usually means its a negative value.

Try changing the format to "general" or something simillar to see if it makes any difference.

cheers
Rob

 

[ipbr21054]

Hi,
It was a negative figure & looking at others cells they had Currency whilsy the ones is question has Custom or Text.

Now changed to Currency all looks better

Thanks

 

[RobP]

Great, glad we could help.

Thanks for the feedback

Rob