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 need it to count how many times a name appears.

Basically it is a list of names and how many times they have not submitted paperwork. 

I have attached a small example to make it easier to understand

I have tried a number of functions, but just cant get it to work, so hoping someone can help

Thanks in advance

Phil

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

11 Replies

  • 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

Resources