Skip to Content

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:

Snap 01.PNG

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

Snap 03.pngSnap 02.png

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.

Snap 04.png

 

  • 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.

Snap 05.png

Please try it and let me know your feedback.

 

Thanks,
Mahboob Mohammed

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Naveen Reddy Yolugoti

    Hi Mahaboob,

    I am trying to do Document linking and I want my text color to be red, but I am unable to get it. Can you please help me here.

    =”<a style=text-decoration:none; color:red; + “‘ href=\”../../opendoc/openDocument.jsp?iDocID=AWl9OBDWX91LjBvCx43q7b4&sIDType=CUID&sType=wid&sRefresh=Y\”
    title=\”\” target=\”_blank\” nav=\”doc\”>*Different Types of Terminations and their respective average wait time and total number of proceedings can be found by clicking here.</a>”

     

    FYI, we are using BO 4.1 SP 9 and I ran the report in chrome and explorer but I don’t see text color applying anywhere.

    (0) 
    1. Mahboob Mohammed Post author

      Hi Naveen,

      How about adding a double quote after color:red? It’d be nice if you can get help from someone who knows HTML, it’ll be very easy for them.

      Thanks,

      Mahboob Mohammed

      (0) 

Leave a Reply