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


"Seanie" wrote in message
...
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

On Nov 18, 12:05*pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message

...



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