use macro to convert number format to text in Excel

Question

I'ved created a macro in Excel that I use to convert column number format to Text so the number format does not change after copy/past from a text file:

Sub ConvertBitColumn()
' ConvertBitColumn Macro
' This macro converts 3rd column of the worksheet to Text.
Dim sht As Worksheet
Set sht = ActiveSheet
sht.Columns(2).NumberFormat = "@"
End Sub

The code works fine, but the column in questin shows some green at the left hand corner for some cells, is there a way to get rid of it, or it is normal? Thanks for responding.

Amy

Answer 1

The green triangle indicates that the cell has a number that's stored as text. Excel is warning you that you can't do math formulas on numbers that are formatted as text cells. Since it sounds like you actually want numbers as text based on your macro, you can turn off error checking in Excel to get rid of those pesky green triangles:

1. Click the File tab.
2. Under Help, click Options.
3. In the Excel Options dialog box, click the Formulas category.
4. Under Error checking rules, clear the Numbers formatted as text or preceded by an apostrophe check box.
5. Click OK.