Jim

If you are looking to avoid mentioning the range in code; there is an
alternative. The range mentioned in your code which is applicable for all
sheets is "I17:I65,K17:K65,K69:K73,I67,D12:D13,K16" mention this as a string
(without quotes ) in a unused cell in a sheet. You can even change this
without touching th e code. Try the below

Sub Macro()

Dim strRange As String, strFormat As String
Dim ws As Worksheet, sh As Worksheet

Set ws = Sheets("Contract Data")
ws.Range("C7") = Me.combobox1.Text

'The below line can be removed if the range is to be taken from cell J1
strRange = "I17:I65,K17:K65,K69:K73,I67,D12:D13,K16"
'strRange = ws.Range("J1").Text 'If range is stored in cell J1

Select Case Trim(Me.combobox1.Text)
Case "$"
strFormat = "[$$-409] #,##0.00;[Red]-([$$-409] #,##0.00)"
Case "£"
strFormat = "£ #,##0.00;[Red]-(£ #,##0.00)"
Case "‚¬"
strFormat = "‚¬ #,##0.00;[Red]-(‚¬ #,##0.00)"
Case "GEL"
strFormat = "[$GEL -437]#,##0.00;[Red]-([$GEL -437]#,##0.00)"
End Select

ws.Range("G4:G34,C13:C14,C20").NumberFormat = strFormat
For Each sh In ActiveWorkbook.Sheets
sh.Range(strRange).NumberFormat = strFormat
Next sh

End Sub


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