Trouble with this code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this macro to force the cells to a negative value. The only thing
is I only need it to force a negative value if there is data in the cell.
This code is filling the empty cells with a value of $ 0.00. I need those
cells to remain empty but just not sure how to alter the code to do that.
Any help you can give is very much appreciated. Thank you


Sub ForceCellsToNegative()

Dim Rng As Range
Dim rCell As Range

Set Rng =
Range("B85:B86,G20:G31,G33:G44,G46:G57,G82,G85:G86,B91,G7:G8,G77:G79,G83,G89:G90")

For Each rCell In Rng.Cells
With rCell
..Value = -Abs(.Value)
..Font.Name = "Arial"
..Font.Bold = True
..NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

If Not Rng Is Nothing Then

Else
Exit Sub
End If

End With
Next rCell

End Sub
 
Sub ForceCellsToNegative()

Dim Rng As Range
Dim rCell As Range

Set Rng =
Range("B85:B86,G20:G31,G33:G44,G46:G57,G82,G85:G86,B91,G7:G8,G77:G79,G83,G89:G90")

For Each rCell In Rng.Cells
With rCell
if .Value <> "" then
..Value = -Abs(.Value)
..Font.Name = "Arial"
..Font.Bold = True
..NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
end if
If Not Rng Is Nothing Then

Else
Exit Sub
End If

End With
Next rCell

End Sub
 
Hi Jouioui,

Why are you opening a new thread?

Perhaps try:
'=============>>
Public Sub ForceCellsToNegative()
Dim Rng As Range
Dim rCell As Range

Set Rng = Range("B85:B86,G20:G31,G33:G44,G46:G57," _
& "G82 , G85: G86 , B91, G7: G8 ," _
& "G77: G79 , G83, G89: G90 ")

For Each rCell In Rng.Cells
With rCell
If Not IsEmpty(.Value) Then
.Value = -Abs(.Value)
End If

.Font.Name = "Arial"
.Font.Bold = True
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
Next rCell

End Sub
'<<=============
 
Back
Top