Solved: Macro help

[sujittalukde]

Need a macro which will do the following before print:

• Clear the back ground color (ie No fill ) except the row containing term ?*total*?
• Print the sheet
• Undo the background color
• save the file

Since the code will print the sheet to the default printer, so please make the code in a manner which will allow the user to change the code to set printer to be used by the code irrespective of the default printer already set.

Thanks in advance

[lucas]

Use your recorder to record these actions and then clean it up a bit. If you need help cleaning it up..post what you come up with along with the problems you encounter here. That's how I would Start...

[sujittalukde]

I had tried with recorded macro earlier but the problems are-
1. It is taking a range as can be seen in the code. It should be for the entire sheet except the rows containing the term "*total*".
2. When the code is prssed to run,it is only removing the background color but not printing & not fill back the background color.
A sample sheet is attached containig the recorded macro.

[lucas]

1. It is taking a range as can be seen in the code. It should be for the entire sheet except the rows containing the term "*total*".

We can easily fix the restoring of the background and print but I don't understand your question #1. Could you tell us what you mean? It is currently selecting the range B3 to G12 and removing the formatting..what do you mean by it should be for the entire sheet?

[sujittalukde]

The attached file is only a sample.My actual sheet which is related to details of expenses goes beyond this recorded range At present it is from A1 to G160.More new rows are also added regularly.But it contains background color & printout should come without any back ground color for the sheet. In the sheet, some rows contain subtotals depending upon the group of expenses (But "subtotal function" is not used,it is done manually)Entire sheet means all the data in the sheet to be printed should not contain any background color except rows containing "*total*"(as no standard is used to define subtotal it may be as Total of Travelling expenses, Telephone Total,Grand total,etc.)These rows contains Grey background color and should come in print out.
Perhaps I am able to explain the problem in case u need further clarification,I will be glad to clarify the same.Thanks for the initiative.

[lucas]

I think I understand...give me a few minutes to look at it and see if I can get you started.

[lucas]

See if this might work for you...I basically copy the sheet..rename it..remove all formatting ...replace the formatting in the range B3 to G12.
print it...delete the new sheet...that might not work for you but it's one way to do this.

[sujittalukde]

Code is working in the desired manner but the problems are-
1.Sheet name is changed to "Main" but for me it will not be possible to change as around 25 to 30 sheets are involved where this code need to be applied
2. Can the range be made dynamic ie will ask the user to select a range
I am thinking to use the macro as an addin.

Check if can be done will be very useful to me. Thanks for the effort that u?ve given

[lucas]

You can change that to Activesheet

It helps if you can give all the info up front so we don't have to do the coding several times to adjust for the trickle of information.

This should work assuming that the area you wish to remain grey is in the same range on each sheet and it only prints the activesheet..not all of them...

[sujittalukde]

Sorry that I couldn't provide information upfront though I tried to provide the same. Sorry again and thanks for the code which is running properly.Thanks again.

[lucas]

No problem...just a note for future posts. Hopefully make it easier to get to a solution for you. Be sure to mark your thread solved using the thread tools at the top of the page.