Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Issue | Excel Discussion (Misc queries) | |||
New issue with cell formatting | Excel Discussion (Misc queries) | |||
Cell formatting issue | Excel Discussion (Misc queries) | |||
Cell formatting issue | Excel Discussion (Misc queries) | |||
VBA - Formatting issue.... | Excel Programming |