Format Cell

[Martin]

Hello,

I am new to Excel programming so I would appreciate some guidance.

I would like to loop through the second row of each column to find the cell
format and then format to a specific way. For instance, if the cell is
general or text then left alight, if it is a number then right align and
format to "0,000", if date then centre align and format dd/mm/yy.

Can someone point me in the right direction here. I have tried piece
together code from this site but cannot make much sense of it.

Many thanks,

Martin

 

[Mike H]

Martin,

Right click your sheet tab, view code and paste this in and run it

Sub missive()
lastcol = Range("IV2").End(xlToLeft).Address
Set myrange = Range("$A$2:" & lastcol)
For Each c In myrange
If WorksheetFunction.IsText(c) Then
c.HorizontalAlignment = xlLeft
ElseIf IsDate(c) Then
c.HorizontalAlignment = xlCenter
c.NumberFormat = "dd/mm/yyyy"
ElseIf IsNumeric(c) Then
c.NumberFormat = "0,000"
c.HorizontalAlignment = xlRight
End If
Next
End Sub

Mike

 

[Martin]

Thanks Mike, worked like a treat. It also helps me to understand how it
works now.

Many thanks.

Martin

 

[Brad E.]

Martin, instead of using "0,000", you should consider "#,##0". This will
always give you a number (the zero), but not leading zeroes.

 

[Martin]

Thank you. I reaslised my mistake when I ran it for the first time.

Martin