Problem with Code to Change Font Colour Q

[Seanie]

I have the code below, given from this NS, which changes all negative
values to red font, but I'm getting a type mismatch error on line-
If c.Value < 0 Then

I've checked the sheet name spelling etc, but all looks ok

Sub ChangeFormatstoRed_whenNegative()

Application.ScreenUpdating = False
Sheets("Register").Activate
For Each c In ActiveSheet.UsedRange.Cells
If c.Value < 0 Then
c.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
End If
Next
End Sub

 

[Peter T]

Maybe c.Value is an error cell, or something that fails when doing the
lessThan comparison

Not sure why you are testing each cell, simply decide the range you are
concerned with and do

dim rng as Range
set rng = Range("A1:B10")

rng.NumberFormat = "#,##0.00;[Red]-#,##0.00"

Regards,
Peter T

 

[Seanie]

Maybe c.Value is an error cell, or something that fails when doing the
lessThan comparison

Not sure why you are testing each cell, simply decide the range you are
concerned with and do

dim rng as Range
set rng = Range("A1:B10")

rng.NumberFormat = "#,##0.00;[Red]-#,##0.00"

Regards,
Peter T

I have the code below, given from this NS, which changes all negative
values to red font, but I'm getting a type mismatch error on line-
If c.Value < 0 Then

I've checked the sheet name spelling etc, but all looks ok

Sub ChangeFormatstoRed_whenNegative()

Application.ScreenUpdating = False
Sheets("Register").Activate
  For Each c In ActiveSheet.UsedRange.Cells
    If c.Value < 0 Then
      c.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    End If
  Next
End Sub- Hide quoted text -

- Show quoted text -

Thanks Peter, I can't see any obvious errors

Used your method, seems to work