Cell Formatting Issue
 

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S. These
are title rows to be printed and the text is centered. It seems that each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other cells?

Thanks in advance

Cell Formatting Issue
 

Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S. These
are title rows to be printed and the text is centered. It seems that each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other cells?

Thanks in advance

Cell Formatting Issue
 

Thanks for replying and I just tried your code and received a Type Mismatch
on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Adjusted Code
 

After looking at your helpexcel.com page I found some code and adjusted mine

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not rng.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next


I changed the format to 0.00 to test, the range didn't change and cells A-I
are selected.

I don't know what to do now.


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Cell Formatting Issue
 

Henry,

My apologies.

set rng=Range("D6:D555")

The select method should not be there.

--
http://HelpExcel.com




"Henry Jones" wrote:

Thanks for replying and I just tried your code and received a Type Mismatch
on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Cell Formatting Issue
 

Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Still not working
 

Thanks, I caught and changed the rng.NumberFormat to cl.Numberformat, here
is my adjusted code,

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next

but the range doesn't change to 2 decimal places.... What is wrong?

Thanks,

H


"Bob Phillips" wrote in message
...
Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Adjusted Code
 

Instead of selection.numberformat, it should be cl.numberformat


--
http://HelpExcel.com




"Henry Jones" wrote:

After looking at your helpexcel.com page I found some code and adjusted mine

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not rng.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next


I changed the format to 0.00 to test, the range didn't change and cells A-I
are selected.

I don't know what to do now.


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Fixed it !
 

I fixed it and now it works. Thanks very much.

Henry

"galimi" wrote in message
...
Instead of selection.numberformat, it should be cl.numberformat


--
http://HelpExcel.com




"Henry Jones" wrote:

After looking at your helpexcel.com page I found some code and adjusted
mine

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not rng.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next


I changed the format to 0.00 to test, the range didn't change and cells
A-I
are selected.

I don't know what to do now.


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code
to
determine if the cell selected within the merge cell is NOT your
heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to
format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Cell Formatting Issue
 

Thanks Bob.
--
http://HelpExcel.com




"Bob Phillips" wrote:

Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Fixed!
 

Hi Bob,

I didn't see the cl.numberformat you had changed. It works now,

Thank you
"Henry Jones" wrote in message
...
Thanks, I caught and changed the rng.NumberFormat to cl.Numberformat, here
is my adjusted code,

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next

but the range doesn't change to 2 decimal places.... What is wrong?

Thanks,

H


"Bob Phillips" wrote in message
...
Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code
to
determine if the cell selected within the merge cell is NOT your
heading and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Still not working
 

You need to change selection.numberformat to cl.numberformat
--
http://HelpExcel.com




"Henry Jones" wrote:

Thanks, I caught and changed the rng.NumberFormat to cl.Numberformat, here
is my adjusted code,

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next

but the range doesn't change to 2 decimal places.... What is wrong?

Thanks,

H


"Bob Phillips" wrote in message
...
Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Still not working
 

Henry,

I have copied an example to http://www.HelpExcel.com/examples

The name is decimal.xls


--
http://HelpExcel.com




"Henry Jones" wrote:

Thanks, I caught and changed the rng.NumberFormat to cl.Numberformat, here
is my adjusted code,

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next

but the range doesn't change to 2 decimal places.... What is wrong?

Thanks,

H


"Bob Phillips" wrote in message
...
Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code to
determine if the cell selected within the merge cell is NOT your heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to S.
These
are title rows to be printed and the text is centered. It seems that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance

Thank you
 

Thanks for the code sample. I did get it working and I appreciate your help
"galimi" wrote in message
...
Henry,

I have copied an example to http://www.HelpExcel.com/examples

The name is decimal.xls


--
http://HelpExcel.com




"Henry Jones" wrote:

Thanks, I caught and changed the rng.NumberFormat to cl.Numberformat,
here
is my adjusted code,

Dim rng As Range
Dim cl As Range

Set rng = Sheet1.Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
Selection.NumberFormat = "0.00"
End If
Next

but the range doesn't change to 2 decimal places.... What is wrong?

Thanks,

H


"Bob Phillips" wrote in message
...
Few bugs in the code

Set rng = Range("D6:D555")

For Each cl In rng
If Not cl.MergeCells Then
cl.NumberFormat = "0.0000"
End If
Next

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Henry Jones" wrote in message
...
Thanks for replying and I just tried your code and received a Type
Mismatch on the following line:

set rng=Range("D6:D555").Select

Any ideas?

Thanks,

Henry

"galimi" wrote in message
...
Henry,

I would change the code to something like this

set rng=Range("D6:D555").Select

for each cl in rng
if not rng.mergecells then
Selection.NumberFormat = "0.0000"
end if
next

This will skip the mergecell selections. You can expand on this code
to
determine if the cell selected within the merge cell is NOT your
heading
and
then format that appropriately.
--
http://HelpExcel.com




"Henry Jones" wrote:

On my worksheet (Excel 2003), I am using the following code to
format:

Range("D6:D555").Select
Selection.NumberFormat = "0.0000"


The problem is every 40 or so cells I have merged 5 rows from A to
S.
These
are title rows to be printed and the text is centered. It seems
that
each
cell on the page has been formatted to 0.0000

How can I just format the range D6:D555 without formatting the other
cells?

Thanks in advance