Conditional Formatting Based on Hyperlink

Question

Hey all, I'm trying to use conditional formatting to find duplicate links between two separate documents, however I cannot seem to find a way to do this. I want to use strike-through to identify the duplicate links, I've created a separate document that has very similar formatting to the one that I am working with.

(The display text and links are various Kevin Macleod songs)

Might I add that the display text and the links between the two separate documents aren't as clear as this example document, so I cannot use conditional formatting based on the display text, it has to be based on the hyperlink.

I'm also pretty new to Excel, so go easy on me 😅

Answer 1

Right-click on the sheet tab
Choose "View Code"
Within the menu click Insert \ Module
Paste in the code below
Close the VBA editor
Apply this formulas in the sheet

C2: =ShowHyperlink([@Example])
D2: =ShowHyperlink([@Example2])

Add a conditional formatting on B2:B6 using this formula

=COUNTIF($C:$C,$D2)>0

That's it.

Andreas.

Function ShowHyperlink(ByVal Where As Range) As String
ShowHyperlink = Where.Hyperlinks(1).Address
End Function

Answer 2

Unfortunately, I cannot do the latter, company policy.

Here is the sample file:

https://docs.google.com/spreadsheets/d/1xI6w36ii7VYgOcjhhUlc-2t-V_WJrOd3/edit?usp=sharing&ouid=115809871693763939729&rtpof=true&sd=true

Would be best to download it so that it doesn't use Google Sheets.

Answer 3

It is far too complicated to recreate such a scenario. And if it does not match yours, then our solution will not work for you.

For this kind of requests, please create a sample file with the layout of your original file, filled with sample data and colored cells with the expected result.

At best make a copy of your original file and anonymize the necessary data. For this please download this file
https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAnonymize.bas?dl=1
Open your Excel file
Right-click on the sheet tab
Choose "View Code"
Press CTRL-M
Select the downloaded file and import
Close the VBA editor
Select the cells with the confidential data
Press Alt-F8
Choose the macro Anonymize
Click Run

Upload it on OneDrive (or an other Online File Hoster of your choice) and post the download link here.
https://support.office.com/en-us/article/Share-OneDrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.