title: VBA Format all Cells as text
body: <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>