Hi Mohammed, There are several possibilities that come to mind.
You can try the following approach, or if this does not do what you want, provide a sample file and I/we will try to give you a different suggestion for your specific situation. 1. Remove all existing subtotals, totals, and page breaks. 2. Insert a new column on the left side of your data. 3. In A1, type the heading "Page" for the column. 4. In A2, enter the desired page number for each row of data, or use a formula to calculate it. Here is an example formula that will change page numbers ever 30 rows: ="Page "&INT((ROW()-1)/30)+1 5. Copy the formula all the way down to the last row of your data. 6. Go to DATA>Subtotal (or Alt-D,B) 7. For "At each change in", select "Page". 8. Place a checkmark next to "Page break between groups". 9. Select other options as desired and hit OK. 10. Optionally hide column A. Or, you may like the effect of making the column the minimum width of 0.06, which is effectively hidden, except when column B is blank, the text from column A will display in it's place. This means you will see the words "Page 1 Total", etc., and "Grand Total", if room in column B, without having to show column A. Then--Presto, you're done! 11. To be certain total rows have not been changed in error after the above procedure, next time just choose DATA>Subtotal again with "Replace subtotals" to make sure all formulas are corrected if necessary. Asa Ø P.S. I added some more information to the above steps from the first copy of this emssage you will have received. Also, when posting to mailing lists/groups, it is best to disable the REPLY-TO address on your messages, since it forces replies to go directly to you instead of the group. I accidentally sent this message directly to you first due to this. Also, since you have your REPLY-TO address the same as your actual FROM address, there is never any need for you to specify a REPLY-TO address. Replies to your personal emails will always return to you by default. _____ -----Original Message----- From: Microsoft Excel Developers List [mailto:exce...@peach.ease.lsoft.com] On Behalf Of Shahid Khan Sent: Monday, April 02, 2012 6:10 AM To: exce...@peach.ease.lsoft.com Subject: Color the Sum Range Hi, I have BOQ (Bill of Quantities) in excel where end of each page carried a page total. I want to check the range of the sum and color it so I can 100% sure that the sum is carried on each page is belong to that page only. Because I have experience some time by mistake it range beyond the current page and the page sum shows the wrong value. I want to audit my sheet, if any sum value is coming from another page it should color the range so it can easily be identified that the sum is wrong and need to check the formula. Expert help really been appreciated. Thanks & regards, Mohammed -------------------------------------------------------------------------- The EXCEL-L list is hosted on a Windows 2003 Server running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see <http://peach.ease.lsoft.com/archives/excel-l.html> http://peach.ease.lsoft.com/archives/excel-l.html . -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
