"Worksheet change" not changing cell on other sheet to match target cell "fill".

If I change the "fill" on the target cell in Sheet 1, I want the same cell on Sheet 2 to change fill automatically to match.

Attached file 151002 Match Fill.xlsm shows it works using this Macro on cells E2:

Please Login or Register  to view this content.

but doesn't work as this "worksheet change event" using B2 as the target, which is what is actually needed:

Please Login or Register  to view this content.

Suggestions, pointers and solutions welcomed as ever

Ochimus

Only changing the content of the cell will trigger the Worksheet_Change event, not changing the format.

You could do it with Selection_Change by holding the address of the selected cell in a static variable, then using that when the next change happens, however that wouldn't work with the cell that is already selected when you open the workbook, so it's not perfect, but that could be trapped with Workbook_Open.

Appreciate the time everyone is giving to this, but think it's "back to the drawing board", because this short cut isn't working.

I used one cell as an example in the attachment, but the "real" job has a large stock sheet (100k+ rows and 20 cols), updated regularly against sheet supplied by wholesaler.

I highlight cells that have changed by "filling" using Conditional Formatting. Which, of course, disappears when I copy the updated cell across, because both sheets now match.

I was hoping that a "worksheet change" approach would "fill" the relevant cells automatically on the Stock sheet, so they would remain "filled" when I copy the update across.

As both sheets are the same dimension, I think I'll have to slog through both sheets, and fill and copy cells that don't match.

I tried the following, but

Please Login or Register  to view this content.

Annoying that it should be straightforward.

Ochimus

It is and isn't straight forward.

When you copy formatting from a cell, you copy the actual (permanent) format, not the conditional (temporary) format.

Could you provide us with a small sample workbook with some fictional data to show what you want done?

Clear the conditional formatting rules from the sheet, then try this.

Please Login or Register  to view this content.

jason,

Thanks to you and everyone else contributing, file now works perfectly.

Annoying that I've wasted a week trying to solve this.

Here's to the next challenge. . . .

Ochimus