For Each Column

[Michael Smith]

Looking to have a macro look through all data in a spreadsheet and
format it based on criteria.
If data is greater than 1 format as $
If data is less than 1 then format as a %.


Thanks in advance!-Mike


*** Sent via Developersdex http://www.developersdex.com ***

 

[Joel]

Sub FixNumbers()

for each cell in cells
if isnumeric(cell) then
if cell < 1 then
cell.NumberFormat = "0.00%"
else
cell.NumberFormat = "$#,##0.00"
end if
end if

end sub

 

[Sam Wilson]

This should give you the right idea:

Sub test()

Dim c As Range
Dim ws As Worksheet
Set ws = ActiveSheet

For Each c In ws.Range("A1",
ws.Cells.SpecialCells(xlCellTypeLastCell).Address)
If c.Value < 1 Then
c.NumberFormat = "0.00%"
Else
c.NumberFormat = "$#,##0.00"
End If

Next c

End Sub