copy cell background color in range in one sheet to the same range in another sheet

Hello,
I have next to no experience with vba. I had found some code years ago that did what I am looking for, but have since lost it.

I want to be able to manually change the background color of cells or a whole row in my 'Main' sheet, and have the same color show up in the same cells / row in a second and third sheet. Not conditional formatting, I do the color change directly in the 'Main' sheet.

I use the second, third, etc. sheets as copies of the 'Main' sheet, and have a filter on a column so that it shows only the information for a particular salesperson. One salesperson per sheet (besides 'Main'). I do not enter data directly into these additional sheets, direct data entry is done in 'Main'.

I have the following code, but it's not working right:

Please Login or Register  to view this content.

Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer

With this, the cell color match works fine in the 'John Details' and 'Neal Details' sheets, but does not work at all in the 'Hans Details" sheet - all cells in the 'Hans Details' sheet still have no background color.

Any ideas?

Thank you!

In this code, actually, Hans is the only sheet that should work correctly.

Please Login or Register  to view this content.

Never do this if you don't know exactly what error you are expecting. If an error occurs you will never know about it. For example, if the sheet is Jan Details but you misspelled it in your code as Hans Details, you will never know.

The variable names are very cryptic and so make the code very confusing to read. It is necessary to keep checking back and forth. This has caused you to misspell variable names

Please Login or Register  to view this content.

The second line is causing an error because xI3DRg is not defined, and you are suppressing the error. If you used Option Explicit you would have found this error. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

ALSO

This line of code is not causing your problem but it doesn't do what you think it does:

Please Login or Register  to view this content.

In a Dim statement, each variable must get its own data type. Onl xID3Rg is a Range variable. All the other ones will default to Variant. Do this:

Please Login or Register  to view this content.

Indentation and spacing in the code make it much easier to read.

I have provided a more readable, correct, and tested version of your code.

Please Login or Register  to view this content.

What other formatting is there? Is there formatting in Main that you do not want to copy? If not, then this is much simpler and much faster than your approach.

Please Login or Register  to view this content.

ByteMarks, sorry, that did not seem to work. After putting in that code, when I changed cell background colors in the 'Main' sheet, nothing changed in the other sheets.

Think about providing your file for testing.

It looks like you started out by using

Please Login or Register  to view this content.

in sheet Main, which will copy the formatting every time you click the mouse anywhere in sheet Main. Yes, that will slow you down.

The other option is to change the code to be triggered by a button click.