highlight duplicates in one or more columns, only if the cell has a value inside (visible)

[Alexander2020]

I have this code that works for the first part, I mean to highlight the duplicates in the column "B", for two different sheets, in an active worksheet. I have more than two worksheets, but I posted the code to look more simple.

Please Login or Register  to view this content.

And I wanted to make it work for two columns for each sheet. I don't know how to add for one more column (my other column is "F")... but I added in the code above an identical line as this:

Please Login or Register  to view this content.

So far so good... now the code finds the duplicate values in both columns, "B" and "F".
My problem is that in the F column the values are the result of some formula. Sometimes the cells look empty (blank), but there is a formula there. The above code considers these apparently blank cells also duplicates and colors them. I need it to color the duplicates only if there is a visible value in those cells. I have seen on the net I should use the functions ISBLANK or ISEMPTY with true or false, to get the second condition fulfilled. But it is beyond my knowledge to write the new "if" function.

Therefore... Please help me!

[Vraag en antwoord]

Why not with conditional formatting?

Please Login or Register  to view this content.

[Alexander2020]

I thought it would be easier to just add a condition inside the "if" function.

I also changed what I wrote since I saw you added a VBA.

[Vraag en antwoord]

I changed my message. See #2

[Alexander2020]

OK... I'll try it... but can you make it work for two columns in each sheet? "B" and "F" columns?

[Vraag en antwoord]

That also happens. Try it

[Alexander2020]

Thank you very much! It is working 100%. How did you tell to look in the "F" column? It must be this trick: cl.Offset(, 4)... the 4th column from "B" is "F".
Anyway... You are a genius in VBA coding... You made it work so fast!

[Alexander2020]

Offtopic, but somehow related to the solution I got in this thread... Is it possible to write in a shorter form?

Application.Sum(Offset(, 1), Offset(, 2), Offset ( ,3), Offset (,4))
Something like:
Application.Sum(Offset(, 1): Offset (,4))
or
Application.Sum(Offset(, 1)- Offset (,4))

I know the above is not working... but if I have 20 such consecutive Offset cells... ???