Change colors of all values when one cell has the focus

[Proventus]

Hi, Hope you can help.

I have a worksheet with thousands of values (in B:B). Not all cell values are different. Some cells are unique, but some cells have identical values.

I have a list of unique values (in F:F). When I select a cell in F:F, I'd like to change all other identical values in the worksheet to color(7).

I have this I found here, but it only highlights the current selection.

Please Login or Register  to view this content.

Many Thanks

[Proventus]

Found a solution and posting in case someone needs it.

Part1

Please Login or Register  to view this content.

Part 2

Please Login or Register  to view this content.

[mikerickson]

There is another way to do this.

First create a named value
Name: MyValue RefersTo: =4

Then Select B1, apply Conditional Formatting with the formula =(B1&""=myValue&"")

Then put this code in the sheet's code module.

Please Login or Register  to view this content.