Solved: how to avoid LITTLE GREEN TRIANGLE error

[DaveK]

Hi all,

Thanks again for your help in the past weeks. I have this little procedure which adds a PLUS SIGN in front of positive numbers for a certain specific report... and it works fine, EXCEPT that I get that little green triangle in the upper lefthand corner of the pasted cell with the string value in it... and with the error 'number stored as text".

I then have to manually go into all the cells and do the little pull-down and select "ignore error"... to get the green triangle to go away.

I have too many cells to do this too all the time, so I hope there is a way in this VBA to make it work WITHOUT this error popping up.

Can someone help?

THANKS!
Dave


Sub Add_the_PLUSSIGN()
Dim ICT As String
Dim row As Integer

Columns("L:L").Select
Application.CutCopyMode = False
Selection.NumberFormat = "@"

For row = 5 To 95

Range("I" & row).Select
Selection.Copy

If Selection.Value > 0.01 Then

ICT = Selection.Text
ICT = "+" & ICT
Range("L" & row) = ICT
Range("L" & row).Select

With Selection.Font
.FontStyle = "Bold Italic"
.ColorIndex = 3
End With

Else

Range("M" & row).PasteSpecial xlPasteAll

ICT = Selection.Text
Range("L" & row) = ICT
Range("L" & row).Select

With Selection.Font
.FontStyle = "Bold Italic"
.ColorIndex = 1
End With
End If
Next

End Sub

[Kenneth Hobs]

You can set the options but I recommend setting them back when your workbook closes or focus changes to another.
Application.ErrorCheckingOptions.IndicatorColorIndex = 5 '5=blue, 2=white
Application.ErrorCheckingOptions.NumberAsText = False

[mdmackillop]

Why not use custom formatting to add the + sign, then there is no need to use text values

Columns("L:L").NumberFormat = """+""0.00;""-""0.00;""- """


Try to avoid selecting cells in the code.

Sub Add_the_PLUSSIGN()
Dim ICT As String
Dim row As Integer
Columns("L:L").NumberFormat = """+""0.00;""-""0.00;""- """
For row = 5 To 95
If Range("I" & row).Value > 0.01 Then
With Range("L" & row)
.Value = Range("I" & row).Value
With .Font
.FontStyle = "Bold Italic"
.ColorIndex = 3
End With
End With
Else
Range("I" & row).Copy
Range("M" & row).PasteSpecial xlPasteAll
With Range("L" & row)
.Value = Range("I" & row).Value
With .Font
.FontStyle = "Bold Italic"
.ColorIndex = 1
End With
End With
End If
Next
End Sub

[marshybid]

Hi there,

Another option would be to switch off 'Automatic Background Checking' - Go to Tools>Options>Error Checking and untick the box

Marshybid