Conditional font coloring of hyperlinked columns

[mhmohammed]

I've seen quite a few times, people requesting how to conditionally change font color of hyperlinked columns depending on the values in the cell. Let's see how to approach and address that request. I'll be using the below sample data for this exercise:



After applying a hyperlink on the Company Name (with the website value), let's see how can we change the font color of Apple to Aqua, Google to Green, Oracle to Orange, and so on.

 

Steps to add hyperlink:

• Right click on the Company Name column -> Linking -> click Add Hyperlink


• In the Link to Web page, in the space given to enter the web address, write the formula =[Website] and click Parse. (we already have the link value in [Website] object.


• Choose the Target window as New window


• In Tooltip, click the down arrow on the right, click build formula, write formula as ="http://"+[Website], and click OK


• Click Apply and OK

Steps to format hyperlink:

• Click inside the cell of one of the Company Names to see the html code in the Formula bar, which would be ="<a href=\"=[Website]\" title=\""+("http://"+[Website])+"\" target=\"_blank\" nav=\"web\">"+[Company Name]+"</a>" as shown below.

 

• Between "a" and "href" in the "a href" in the beginning , add the text style='text-decoration:none;" +  (If([Company Name]="Apple") Then "color:aqua" ElseIf([Company Name]="Facebook") Then "color:#4766A9"  ElseIf([Company Name]="Google") Then "color:green" ElseIf([Company Name]="Oracle") Then "color:red" ElseIf([Company Name]="SAP") Then "color:orange") + "' and parse and apply the formula.

 

Note: The whole text in the cell would now be:

="<a  style='text-decoration:none;" +  (If([Company Name]="Apple") Then "color:aqua" ElseIf([Company Name]="Facebook") Then "color:#4766A9"  ElseIf([Company Name]="Google") Then "color:green" ElseIf([Company Name]="Oracle") Then "color:red" ElseIf([Company Name]="SAP") Then "color:orange") + "' href=\"=[Website]\" title=\""+("http://"+[Website])+"\" target=\"_blank\" nav=\"web\">"+[Company Name]+"</a>"

 

This is what the result looks like, Facebook is showing up before Apple, because of the color code we're using for Facebook will come before color name we're using for Apple, when sorted. We can add Company Name in the beginning, so it sorts using Company Name and then hide that extra column by right clicking on it -> Hide -> click on Hide Dimension.



Please try it and let me know your feedback.

 

Thanks,
Mahboob Mohammed