LoopThrough Workshhet for Worksheet_change

[suryagoel]

Hi

I have the following code in my worksheet, which I would like to run through all the sheets automatically, even if there is new sheet added. Also, if you could optimize the following code and for a certain range of cells that would be awesome...Thanks XX

Also, please mention where i should plug this, in all individual sheets, or in the workbook, or in the module....thanks

-------------------------------------------------------

Please Login or Register  to view this content.

------------------------------------------------------

[ajaykgarg]

You can loop through all sheets as following:-

Please Login or Register  to view this content.

You shoud decide where to put the code, depending on your requirement.

hth
Ajay

[teylyn]

suryagoel,

I'm not a VBA wiz, but I've noticed a few things:

you differentiate between

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

You could do away with the first part of the IF statement, because the Else takes care of the situation even if there is only one cell in Target.

If you want to loop through all sheets in the workbook, I'm not sure you can use Target, since that is particular to the Worksheet_Change event of the current sheet.

You'd need some other way of identifying the cells that you want to apply the format to.

I wonder if the Worksheet_Change event is the right trigger for this operation in the first place, with regards to checking through all other sheets in the workbook.

Maybe you can post a sample file and explain in context what you want to do.

As an alternative to ajaykgarg's code to loop through all sheets in a workbook, you can use

Please Login or Register  to view this content.

cheers

[suryagoel]

You can loop through all sheets as following:-

Please Login or Register  to view this content.

You shoud decide where to put the code, depending on your requirement.

hth
Ajay

Ajay

It's not working, I am very novice to this, Could u please combine your code and mine to give me exactly what I need. I need a copy and paste solution for my code to work....Please bare

[teylyn]

suryagoel,

you can not use Target on another sheet. That's why you can not use your original code on other worksheets.

Identify the common reuirements to identify the cells that need to be formatted. In your current sheet it is the Target cell(s), i.e. the cell(s) that was/were changed with the last operation. Which cells in the other sheets should be formatted?

[suryagoel]

I am attaching herewith a sample file....

Surya

[teylyn]

Instead of attaching a massive file, please describe in plain words what you want to do. If you can not describe the specifications of your requirement, I doubt that anyone will be able to guess them by trolling through a large, unfamiliar sheet.

When you've described what you want to do, you can attach a small data sample, -- emphasis on small and sample -- where you mock up your expected results, so the helpers here have a means of testing and verifying their suggestions.

If you have a workbook with several sheets, a data sample with a few sheets and a few dozen rows of sample data will be sufficient to illustrate your goal. That should take less than 50 Kb unzipped.

cheers