changeNumberFormat macro

[Todd]

Hi, I have a set of worksheets that i want to change the
number formatting on. In some columns there are
percentages, always with a header "%" and the rest I want
to be in accounting format but is in several different
number formats. Some cells are text only. I am working
on a macro to search the worksheet for cells that aren't
percentages and make them in the accounting format. Heres
what I have, can you see where I am going wrong?

Sub ChangeNumberFormat()
For Each cell In sht.UsedRange
IF Selection.NumberFormat = "0.00%"
End If
With Selection
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_
(* ""-""??_);_(@_)"
Next cell
MsgBox "Process Complete"
End Sub

 

[Dave Peterson]

You've kind of got a mixture of things going on.

Maybe this'll give you some more ideas:

Option Explicit
Sub ChangeNumberFormat()
Dim myCell As Range

For Each myCell In Selection.Cells 'sht.usedrange.cells
If myCell.NumberFormat = "0.00%" Then
'do nothing
Else
If IsNumeric(myCell.Value) Then
myCell.NumberFormat _
= "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End If
End If
Next myCell

MsgBox "Process Complete"
End Sub

I used selection (instead of .usedrange). I figured it would be easier for you
to limit the range by selecting the portions that could have number formats that
should be changed.

 

[Tom Ogilvy]

Sub ChangeNumberFormat()
Dim sh as Worksheet
Dim cell as Range
set sh = worksheets("Sheet1")
For Each cell In sht.UsedRange.SpecialCells(xlFormulas)
IF instr(cell.NumberFormat,"%") = 0 then
if cell.hasFormula then
if isnumeric(cell) then
cell.NumberFormat = "_(* #,##0.00_);" & _
"_(* (#,##0.00);_(* ""-""??_);_(@_)"
End If
End If
Next cell
MsgBox "Process Complete"
End Sub