Problem with Code to Change Font Colour Q

  • Thread starter Thread starter Seanie
  • Start date Start date
S

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
 
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
 
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
Click to expand...
I've checked the sheet name spelling etc, but all looks ok
Click to expand...
Sub ChangeFormatstoRed_whenNegative()
Click to expand...
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 -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Peter, I can't see any obvious errors

Used your method, seems to work
 
Back
Top