Help with macro to set vertical alignment in Excel without affecting text indents

[jish]

Hi,

Is it possible to have simple Excel macro to change the cell vertical alignment to center throughout a table without affecting the indents? When doing it manually, one has to first select cells with similar indents to change the alignment---otherwise, the indents are changed to match those of the first selected cell. Alternatively, one can do it cell by cell, which also does not affect the indents.

In a perfect world, it would be wonderful also for the horizontal alignment to be set to left, the text wrap to be turned off, and the number format to be set to text for any cells that don't contain numbers (or at least those that are already not set to "Number")---all without affecting anything else in the cell, like the indents---but these would just be gravy. The real problem is resetting the vertical alignment.

Vertical Alignment = Center
Horizontal Alignment = Left
Wrap Text =False
Number format (only for cells than contain letters) = Text

I'm using Excel 2016. Any help one could give would be greatly appreciated. Obviously, I am aware of the existence macros but don't know how to write them properly. I have searched for a macro that does this, but haven't found one.

Thank you---

[p45cal]

There are quicker ways if this takes too long:
If the table you're talking about is a true Excel Table then this snippet might do it for you (you only need the selected cell(s) to within such a table - the whole table excluding the headers will be processed):

Sub blah()
For Each cll In Selection.ListObject.DataBodyRange.Cells
  With cll
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .MergeCells = False
    If TypeName(cll.Value) = "String" Then .NumberFormat = "@"
  End With
Next cll
End Sub

but if it's not a true table then this snippet will act in a similar way on only the selected cells:

Sub blah2()
For Each cll In Selection.Cells
  With cll
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .MergeCells = False
    If TypeName(cll.Value) = "String" Then .NumberFormat = "@"
  End With
Next cll
End Sub

[jish]

Wow! What a Monday morning gift! The second instance—blah2—is perfect for my needs!

I commented out the "merge cells" line because I found that it was unmerging cells. Also, I might generally comment out the horizontal alignment part, because by doing that it allows me to select the whole table and run it to set the vertical alignment in one swoosh and also unwraps text that has been set to wrap.

It works quickly enough because the worksheets are usually at most three pages long.

I'm so grateful to you for saving me hours (and hours) of work on hundreds of tables AND for making sure that I catch all the wrongly formatted cells.

Thank you again!!!