VBA Format all Cells as text
<p>Below is a Sub that is being used to format all cells as text in the spreadsheet starting with worksheet3 onwards. As you'll see there's a line in bold that I will most likely need to change. One of the purposes of the line is to keep the values of the Column1 ("Client ID") intact because in every sheet it consists '00001' values that converted with this code would change to '1'. That's wrong. Important thing is that Column1 in every worksheet always contains the same '00001' values. </p>

<pre><code>Sub formatAllCellsAsText()

Dim wsTemp As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim Cell As Range

For sht = 3 To Worksheets.Count
    Set wsTemp = Sheets(sht)
    Set StartCell = wsTemp.Range("A4")
    LastRow = wsTemp.Range("A4").CurrentRegion.Rows.Count
    LastColumn = wsTemp.Range("A4").CurrentRegion.Columns.Count
    For Each Cell In wsTemp.Range(StartCell, wsTemp.Cells(LastRow, LastColumn)).Cells

    If Not IsEmpty(Cell.Value) And IsNumeric(Cell.Value) 
    And InStr(wsTemp.Cells(1, Cell.Column), "Client ID") &lt;= 0 Then

        Dim Temp As Double
        Temp = Cell.Value
        Cell.ClearContents
        Cell.NumberFormat = "@"
        Cell.Value = CStr(Temp)
      End If
    Next
Next sht

End Sub
</code></pre>

<p>Now my problem is that in one worksheet there's Column6 ("Value") that I want to preseve 000s in front of a number. When I change the line to the following my macro still removes preceding 000s from the Column6 values. I guess the condition is not as straightforward as I have thought. </p>

<pre><code>If Not IsEmpty(Cell.Value) And IsNumeric(Cell.Value) And 
InStr(wsTemp.Cells(1, Cell.Column), "Client ID") &lt;= 0 And 
InStr(wsTemp.Cells(6, Cell.Column), "Value") &lt;= 0 Then
</code></pre>

<p>Any suggestions?</p>