Simple Tweak in excel converting Negative to Positive and changing FONT color

[Jyotirmaya]

I am using the following code to make all the negative value to positive

Sub test()
For Each Cell In Range("A1:Z10")
If Cell.Value < 0 Then
Cell.Value = Abs(Cell.Value)
End If
Next Cell
End Sub

I want that the converted negative values should be of font color RED.
what should be the tweak for the code ??

 

[Peter_SSs]

Assuming the range is filled with numerical values, try the following in a copy of your workbook.

Sub ConvertAndColour()
  With Range("A1:Z10")
    .Value = Evaluate(Replace("if(#<0,""a"" & #,#)", "#", .Address))
    .SpecialCells(xlConstants, xlTextValues).Font.Color = vbRed
    .Replace What:="a-", Replacement:="", LookAt:=xlPart
  End With
End Sub

 

[Peter_SSs]

If the range could contain text and/or blank cells as well as numerical values, then try

Sub ConvertAndColour()
  Dim a As Variant
  
  With Range("A1:Z10")
    a = .Value
    .Value = Evaluate(Replace("if(len(@),if(@<0,""#N/A"",@),"""")", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).Font.Color = vbRed
    .Value = a
    .Value = Evaluate(Replace("if(len(@),if(@<0,-@,@),"""")", "@", .Address))
  End With
End Sub

 

[Jyotirmaya]

Assuming the range is filled with numerical values, try the following in a copy of your workbook.

Sub ConvertAndColour()
  With Range("A1:Z10")
    .Value = Evaluate(Replace("if(#<0,""a"" & #,#)", "#", .Address))
    .SpecialCells(xlConstants, xlTextValues).Font.Color = vbRed
    .Replace What:="a-", Replacement:="", LookAt:=xlPart
  End With
End Sub

Thank you Peter

 

[lrobbo314]

That was an awesome solution! I've never seen evaluate used that way. Very slick!

 

[Peter_SSs]

Thank you Peter

You are very welcome.

That was an awesome solution! I've never seen evaluate used that way. Very slick!

Thanks for your kind comments.

On further reflection, if there are no text cells (or you don't mind if they go red), only numbers and/or blanks, there was no need to even use Evaluate. This should do just as well.

Sub ConvertNColour()
   With Range("A1:Z10")
    .Replace What:="-", Replacement:="#-", LookAt:=xlPart
    .SpecialCells(xlConstants, xlTextValues).Font.Color = vbRed
    .Replace What:="#-", Replacement:="", LookAt:=xlPart
  End With
End Sub