Match color between two sheets, if not match highlight with different color

[abhinavbinkar]

Hi,

I've two workbooks in which some cells of column D of both the workbooks are in yellow filled color.
I wants following things :
If the colored cell of column D of workbook 1 is not in the same color in workbook 2 then highlight the cell of column C in workbook 2.
If the colored cell of column D of workbook 2 is not in the same color in workbook 1 then highlight the cell of column C in workbook 1.

Please refer the file attached. Thanks


- Abhinav

[MickG]

This code will work, but the cells coloured "Yellow" in the 2 sheets are different "Yellows" !!
This means all the yellow cells will be coloured "Red" in column "C" of both sheets, along with the Mismatched coloured cells.
NB:- The workbooks are called "book1", and "book2"

Please Login or Register  to view this content.

Regards Mick

[abhinavbinkar]

I think it is not working.

Now I've attached 2 new workbooks with the exact same yellow color code.

Please help sir.

[abhinavbinkar]

I think it is not working.

Now I've attached 2 new workbooks with the exact same yellow color code.

Please help sir.

PS: The colored cells on both workbooks were done through Conditional Formatting.

[MickG]

Try this:-

Please Login or Register  to view this content.

Regards Mick

[abhinavbinkar]

Thank you so much Mick. It is now working.

Can you please help me one last time if it is possible, as my file names for comparison are different daily.

"Book2.xlsx" actual file name is in the format as : ECOM STATUS - DD-MM-YY.xlsx and path for this file is:
BOOK2.xlsx = "C:\Users\MRF LTD\Desktop\ECOM DAILY\ECOM STATUS - 02-04-18.xlsx"

"Book1.xlsx" actual file name is in the format as : ECOM STATUS - DD-MM-YY(Today - 1 day i.e. previous date).xlsx and path for this file is:
BOOK1.xlsx = "C:\Users\MRF LTD\Desktop\ECOM DAILY\ECOM STATUS - 01-04-18.xlsx"

Please help sir.

- Abhinav

[abhinavbinkar]

Thank you so much Mick. It is now working.

Can you please help me one last time if it is possible, as my file names for comparison are different daily.

"Book2.xlsx" actual file name is in the format as : ECOM STATUS - DD-MM-YY.xlsx and path for this file is:
BOOK2.xlsx = "C:\Users\MAV LTD\Desktop\ECOM DAILY\ECOM STATUS - 02-04-18.xlsx"

"Book1.xlsx" actual file name is in the format as : ECOM STATUS - DD-MM-YY(Today - 1 day i.e. previous date).xlsx and path for this file is:
BOOK1.xlsx = "C:\Users\MAV LTD\Desktop\ECOM DAILY\ECOM STATUS - 01-04-18.xlsx"

Please help sir.

- Abhinav

Is it possible that both the files to open automatically and to run that macro code for comparison.

[MickG]

Try this:-
NB:- See code Notes.

Please Login or Register  to view this content.

Regards Mick

[abhinavbinkar]

Untitled1.jpg

When i debug the code it is appearing as :

Untitled2.png

Please help.

[MickG]

I Imagine its because your file path and file name are not the same as the one you are trying to open.
NB:- When the code fails on the line "Workbook.open" , then above it you will see the variable "tpth1" hold your curser over it to read the Path and file name, check this line to ensure it is the Exactly same as the one you are trying to open.
If the paths are different change the code path (where previously shown in code) to the correct path

[abhinavbinkar]

The path in that code is different. The path is showing in variable "tpth1" is that folder where the "PERSONAL.xlsb" file is located.
How to change that path to where my actual files are stored at desktop ECOM DAILY folder.

Untitled3.png

My correct path is "C:\Users\MRF LTD\Desktop\ECOM DAILY\ECOM STATUS - DD-MM-YY.xlsx"

Regards,
Abhinav

[MickG]

Change path in code as shown below:-
When changed, step through code "F8" to see if the path and the file name are correct.
When correct change other path for "Pth2"

Please Login or Register  to view this content.

[abhinavbinkar]

WOW!!! That worked like a charm. Truly you're a genius Mick.
Thank you so much for your valuable efforts. God bless you.

[abhinavbinkar]

Hey Mick, I now just required that it should clear all the filled color and conditional formatting from "Column C" in both the files before running that code and start comparing.

Regards,
Abhinav

[MickG]

As the conditional formatting is in columns "D", I take it that you mean the Cells coloured red from the running of the code:-
Add Red lines in Code:-

Please Login or Register  to view this content.

[abhinavbinkar]

Thank you so so so so sooooo... much Mick for the help.

[MickG]

You're welcome