VBA and NumberFormat

[silentsound]

Hi everyone,
Hoping someone might be able to help me with this one. I want to set a custom format for some cells and used the macro recorder to work out what the custom format should look like (so I assume it should be correct formatting) however when I use the numberformatting in my code it causes an error. I am unclear exactly what the .locked and .formulahidden do so added bonus if anyone understands these (I just copied them from the recorder). I should add the numberformat below works correctly if I use it to set a custom format in Excel (without VBA)

This is the problematic code:



For j = 1 To 2
Worksheets(WSName(j)).Activate


With Application.FindFormat
.Clear
.NumberFormat = ReplaceFormatCell.NumberFormat
.Locked = True
.FormulaHidden = False
End With
With Application.ReplaceFormat
.Clear
.NumberFormat = "_(£* #,##0.00_);_(£* (#,##0.00);_(£* ""-""_);_(@_)"
.Locked = True
.FormulaHidden = False
End With


Cells.Replace "", "", xlPart, xlByRows, False, , True, True



To be clear it is the .Numberformat = "..." that causes the error. Any suggestions much appreciated!

Thanks

[Bob Phillips]

.NumberFormat = "_(£* #,##0.00_);_(£* (#,##0.00);_(£* """"-""""_);_(@_)"

[silentsound]

.NumberFormat = "_(£* #,##0.00_);_(£* (#,##0.00);_(£* """"-""""_);_(@_)"

Hi xld,

Thanks for the suggestion. I still get an error, the numberformat won't assign. I've tried using chr(34) without success..any ideas why this might be the case? I'm using Excel 2007 but it's in compatibility mode.

I don't think the zero part of the numberformat is causing the issue; if I remove the zero and text parts (which would still suit me actually if that worked) I get an error.