Forum Discussion

PHILIP ROCHESTER's avatar
PHILIP ROCHESTER
Brass Contributor
Nov 11, 2018
Solved

COUNT CELLS THAT ARE NOT HYPERLINKED OR HAVE A CELL COLOR

Hi i have a worksheet in Excel where i need to count cells (with names, omitting blanks), that have not been hyperlinked (the hyperlinked cells are colored green) and transfer result to a list. I ne...
Formulas and Functions
  • Jamil's avatar
    Jamil
    Nov 11, 2018

    See the attached example.

     

    You can get what you need, by using UDF and COUNTA function.

     

    I have put as an example in T2 this =COUNTA(B2:Q2)-HyperlinkFound(B2:Q2)

     

    and the UDF embedded in the workbook is this

    Public Function HyperlinkFound(Rng As Range)
    HyperlinkFound = Rng.Hyperlinks.Count
End Function
Jamil's avatar
Jamil
Bronze Contributor
Nov 11, 2018

See the attached example.

 

You can get what you need, by using UDF and COUNTA function.

 

I have put as an example in T2 this =COUNTA(B2:Q2)-HyperlinkFound(B2:Q2)

 

and the UDF embedded in the workbook is this

Public Function HyperlinkFound(Rng As Range)
    HyperlinkFound = Rng.Hyperlinks.Count
End Function
  • PHILIP ROCHESTER's avatar
    PHILIP ROCHESTER
    Brass Contributor
    Nov 20, 2018

    Hi Jamil

    thanks for this, it works a treat. However, can i have it either to auto calculate or be ran from button click (Active X button). I have tried to link it to a button, but cant get it to run

    thanks

     

    Phil

    • Jamil's avatar
      Jamil
      Bronze Contributor
      Nov 20, 2018

      Hi Philip,

       

      I have attached  the version that you can populate the formula via macrobutton.

       

      • PHILIP ROCHESTER's avatar
        PHILIP ROCHESTER
        Brass Contributor
        Nov 22, 2018

        Hi Jamil

         

        thanks for that. However, i tried the button and it didn't work. No worries though.

        When i attached the original doc i had all "same" name one one row, where as actually the names differ.

        What i have tried to do (but cant!) is count (as originally describer) the names without hyperlinks. You gave me the code for that - thanks. But what i actually need to count is the whole sheet, as names will not necessarily be in same row, which makes it difficult to accurately count the cells without hyperlinks for a particular person. I have attached a doc which shows it better, thanks and i hope you can help as i am stumped! 

Resources