Force Change Event in workbook

Please can anyone help with the following;

I Have a workbook with various named worksheets which I use to calculate perfomance based on Total No. of items handled divided by total labour hours used giving me a value for Items per Labour Hour. Depending on this value some cells are formatted in a colour in order to draw the users attention to it. I also have a summary sheet where some of the info from each worksheet is displayed. On the summary sheet there is a target value for items per labour hour. The individual worksheets code works fine but I cannot get the worksheet cells to reformat if I ammend the target value on the summary.

I have used the following code on the ThisWorkbook Change event

Please Login or Register  to view this content.

and also
Created a module with the following code

Please Login or Register  to view this content.

Cheers
Colin

Why don't you just use Conditional Formatting on the source sheets?

It might help others to help you if you uploaded a sample workbook with some typical data rather than just post the code out of context.

Regards

This is probably a stupid question but if you change the target value for one row, for example, CHINA, why do you then loop through all the sheets?

Whatever, I think it is this bit of code that is wrong:

Please Login or Register  to view this content.

as in:

Please Login or Register  to view this content.

You say that the comparison is (should be) against cell I6 ... I think you need to count through the alphabet and think again ;-)


Regards

Your target values on the Summary sheet are in column I, which is column 9, not column 10. Hence the reference to that line of code in my previous post.

Regards

Because you have a Sheet Change event in the Workbook module, *any* change on *any* sheet will fire the Change event. So, if you make a Change on the CHINA sheet, you'd do whatever you need to relating to that sheet. If, however, you make a Change on the Summary sheet, I would have thought you only needed to process the sheet that the change related to.

I may be very wrong as I don't follow your logic but it looks to me as though you are processing all 10 sheets every time you make any change on the Summary sheet.

I still don't know why you don't use CF as then you wouldn't need any code.

Regards

First question then: is the problem fixed?

With regard to using CF across sheets, all you need to do is assign a named range to each of the target values, for example, "ChinaT" without the quotes. You will then be able to use the named range (cell) in any formula you use in the CF.

Regards

I'm pleased and not a little relieved that you have a successful outcome. ;-)

I'm still a little bemused as to why you loop through all the sheets when you change *one* target on the summary sheet. If you named the worksheets to match the products listed on the summary sheet you could pass the product name (from the relevant cell) to the format sheet routine and only process the one sheet relating to the changed target. Or am I missing something?

Regards

I think that the attached workbook addresses the issue of looping through all the worksheets.

It's not tested to destruction but it seems to work.

You should now be able to change a target on the summary sheet and have it reflected on the form sheet and vice versa.

Note that I have aligned all the field names on the summary sheet with the tab names. It won't work if they are not aligned in this way.

Regards