Highlight cell if duplicate text on another sheet in workbook.

Are there a fixed number of sheets, or are more sheets added dynamically?

There is no one expression you can use in a Conditional Formatting formula to check multiple sheets in one shot; a sheet array expression is invalid in CF. You could write a single long rule that checks every other sheet, but you would have to write an individual rule for each sheet where you want to apply the formatting. This is pretty easy unless you have a lot of sheets, or if you are dynamically adding new sheets.

This could be done more easily with a macro. My approach would be to write a function that would be referenced in a CF rule, although it would be a Volatile function and so might slow things down. It could also be done with a macro that permanently (instead of conditionally) highlights the cells, which would run anytime there is a change to any cell in column A on any sheet. This could also slow things down, depending on how you are updating the data.

Originally Posted by jackm1249

I want when I enter data in a cell in column A to highlight if that data is already entered in another sheet in workbook, in Column A.

If someone enters a value in Sheet1 that is already entered in Sheet2, you want the new value in Sheet1 to be highlighted. Do you also want the original value in Sheet2 to be highlighted?

If you attach a file I can install a macro that will do what you want. Otherwise I can provide code and you can do the installation.

Originally Posted by 6StringJazzer

If someone enters a value in Sheet1 that is already entered in Sheet2, you want the new value in Sheet1 to be highlighted. Do you also want the original value in Sheet2 to be highlighted?

If you attach a file I can install a macro that will do what you want. Otherwise I can provide code and you can do the installation.

I would be checking 2 columns column A and column H for duplicate values, (Text ) ( all dups, no matter what column it's in A or H)
Highlighting both would be great.

This workbook is on a shared folder and will be updated by many to include 40 or so locations, so I would need it to check infinite number of sheets.
Hope that makes sense.

Our inventory isn't what it should be, and we are getting multiple items at more than one branch.

If workable that would be great sending me Macro.
jackm1249

OK, here is a solution that works for any number of sheets, although run time may be a while for large workbooks.

Every time you change a value in column A or column H of any sheet, it checks for duplicates
It checks for duplicates in column A and column H of every other sheet
If a duplicate is found, it is highlighted as yellow on both sheets
If you change a highlighted value so it is no longer a duplicate, the yellow fill will be removed where you change the value, but not on other sheets where it was duplicated.
It does not look for duplicates within the same sheet.