Trouble with leading 0s in VBA

[mtterry]

I have a column with unique IDs that are supposed to be 7 characters in length. Sometimes when these get manually input the leading 0 is omitted, so I was writing a macro to check, and if the number of characters is 6, to add a 0 to the beginning. In some instances these cells are formatted as Text, in other cases as 'Custom'. To get everything on the same page, I inserted a step that formats them all to Text. When this step occurs, it strips the leading 0 from those cells that were formatted as Custom, then when the rest of the macro runs to check the length of the text string, it is only entering a 0 on a few select cases, not to every cell that appears to only have 6 characters. I'm not sure why this is happening, below is a snapshot of the code that I'm using. The line that highlights the cell was only meant to catch rare instances where someone made a bigger error and perhaps forgot an additional number.

Dim cel As range

Columns("A:A").Select
Selection.NumberFormat = "@"

For Each cel in Range("A1", Range("A" & Rows.Count).End(xlUp))
If Len(cel.Value) = 6 Then cel.Value = "0" & cel.Value
If Len(cel.Value) <> 7 Then cel.Interior.ColorIndex = 6
Next

 

[mikerickson]

How about

For Each Cel in Range("A1", Range("A" & Rows.Count).End(xlUp))
    cel.Value = "'" & Right("0000000" & cel.Text, 7)
Next

 

[mtterry]

That works, thank you

 

[Rick Rothstein]

Here is another way that avoids looping through each cell...

[table="width: 500"]
[tr]
	[td]With Range("A1", Range("A" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF({1},TEXT(" & .Address & ",""'0000000""))")
End With[/td]
[/tr]
[/table]