Jacob,

I forgot to say that these changes are happening on mulitple sheets. I have
a different code that copies sheet 'Cert 1' and creates sheet 'Cert 2' and so
on. These format changes happen to every sheet with 'Cert ' in the title.

"Jacob Skaria" wrote:

In your code what is sh..

Try the below...You can assign the format to a variable...

Dim sh As Worksheet
Dim strFormat As String
Set sh = Sheets("Contract Data")
strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0."
sh.Range("G4:G34").NumberFormat = strFormat
sh.Range("Summary_Gross").NumberFormat = strFormat

If this post helps click Yes
---------------
Jacob Skaria


"Jim" wrote:

I'm having trouble with the following code, it gets stuck at
"sh.Range("Summary_Gross")." What I am trying to do is change the format of
cells, which works fine, but instead of naming cells I would like to name
ranges.

Sheets("Contract Data").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "$"
Range("G4:G34").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
Range("C13:C14").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
Range("C20").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Summary_Gross").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Summary_Rate").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Summary_Prev").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Adj_Gross").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Adj_Rate").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("Adj_Prev").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
sh.Range("K108:K105").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0."
sh.Range("I67").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0."
sh.Range("D12:D13").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0."
sh.Range("K16").NumberFormat = _
"[$$-409] #,##0.00;[Red]-([$$-409] #,##0."


Suggestions?

JIM