Format Cell
 

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

Format Cell
 

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" wrote:

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

Format Cell
 

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

Many thanks.

Martin

"Mike H" wrote:

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" wrote:

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

Format Cell
 

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

"Martin" wrote:

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

Many thanks.

Martin

"Mike H" wrote:

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" wrote:

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

Format Cell
 

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

Martin

"Brad E." wrote:

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

"Martin" wrote:

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

Many thanks.

Martin

"Mike H" wrote:

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" wrote:

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