SOLVED
Home

COUNT CELLS THAT ARE NOT HYPERLINKED OR HAVE A CELL COLOR

%3CLINGO-SUB%20id%3D%22lingo-sub-285110%22%20slang%3D%22en-US%22%3ECOUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-285110%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3Ei%20have%20a%20worksheet%20in%20Excel%20where%20i%20need%20to%20count%20cells%20(with%20names%2C%20omitting%20blanks)%2C%20that%20have%20not%20been%20hyperlinked%20(the%20hyperlinked%20cells%20are%20colored%20green)%20and%20transfer%20result%20to%20a%20list.%20I%20need%20it%20to%20count%20how%20many%20times%20a%20name%20appears.%3C%2FP%3E%3CP%3EBasically%20it%20is%20a%20list%20of%20names%20and%20how%20many%20times%20they%20have%20not%20submitted%20paperwork.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20small%20example%20to%20make%20it%20easier%20to%20understand%3C%2FP%3E%3CP%3EI%20have%20tried%20a%20number%20of%20functions%2C%20but%20just%20cant%20get%20it%20to%20work%2C%20so%20hoping%20someone%20can%20help%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-285110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299298%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299298%22%20slang%3D%22en-US%22%3EGreat!%20Thanks%20for%20the%20feedback.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299268%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299268%22%20slang%3D%22en-US%22%3EPerfect%2C%20thanks%20Jamil%20-%20seems%20so%20easy%20when%20i%20look%20at%20the%20code.%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%20again%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-297390%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-297390%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20philip%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehere%20it%20the%20attachment%20as%20per%20your%20latest%20request.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-297246%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-297246%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ejust%20one%20last%20thing%20-%20i%20need%20to%20have%20the%20calculation%20box%20(with%20the%20names%20and%20numbers%20on)%20and%20the%20macro%20button%20on%20a%20separate%20sheet%20(say%2C%20sheet%202).%20I%20have%20tried%2C%20but%20cant%20get%20it%20to%20work%20-%20could%20you%20help%20again%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhilip%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291382%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291382%22%20slang%3D%22en-US%22%3EHi%20Philip%2C%3CBR%20%2F%3EYou%20are%20very%20welcome%20and%20thanks%20for%20your%20kind%20words.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291350%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291350%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3CBR%20%2F%3E%3CSTRONG%3Eyou're%20a%20genius%3C%2FSTRONG%3E%20-%20i%20have%20tried%20numerous%20codes%20etc%2C%20but%20not%20been%20able%20to%20get%20this.%20(The%20only%20way%20i%20was%20able%20to%20get%20this%20was%20through%20creating%20a%20macro%20to%20sort%20into%20fill%20and%20no%20fill%20cells%20%5B%3CEM%3Efor%20hyperlinks%3C%2FEM%3E%5D%20and%20then%20use%20custom%20sort%20and%20functions%20for%20the%20names%20to%20get%20the%20totals%20-%20but%20this%20is%20too%20long%20a%20function%20and%20it%20slowed%20my%20doc%20down)%2C%20yet%20what%20you%20have%20shown%20me%20.......%3CU%3E%3CSTRONG%3Ethis%20is%20exactly%20what%20i%20need%3C%2FSTRONG%3E%3C%2FU%3E.%20Thank%20you%20so%20much%2C%20you%20have%20helped%20me%20no%20end.%20I%20can%20now%20eventually%20finalise%20my%20doc%2C%20thanks%20to%20you%20%F0%9F%91%8D%F0%9F%91%8D%F0%9F%91%8D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291276%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291276%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Philip%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ehere%20is%20the%20modified%20UDF.%3C%2FP%3E%0A%3CP%3EI%20have%20also%20embedded%20this%20into%20the%20workbook.%20you%20can%20run%20the%20macro.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EFunction%20myCountIf(rng%20As%20Range%2C%20criteria)%20As%20Long%0A%20Counter%20%3D%200%0AFor%20Each%20cell%20In%20rng.Cells%0A%20%20%20%20If%20cell.Value%20%3D%20criteria%20And%20cell.Hyperlinks.Count%20%3D%200%20Then%0A%20%20%20%20Counter%20%3D%20Counter%20%2B%201%0A%20%20%20%20End%20If%0ANext%20cell%0AmyCountIf%20%3D%20Counter%0AEnd%20Function%0A%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290449%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290449%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20that.%20However%2C%20i%20tried%20the%20button%20and%20it%20didn't%20work.%20No%20worries%20though.%3C%2FP%3E%3CP%3EWhen%20i%20attached%20the%20original%20doc%20i%20had%20all%20%22same%22%20name%20one%20one%20row%2C%20where%20as%20actually%20the%20names%20differ.%3C%2FP%3E%3CP%3EWhat%20i%20have%20tried%20to%20do%20(but%20cant!)%20is%20count%20(as%20originally%20describer)%20the%20names%20without%20hyperlinks.%20You%20gave%20me%20the%20code%20for%20that%20-%20thanks.%20But%20what%20i%20actually%20need%20to%20count%20is%20the%20whole%20sheet%2C%20as%20names%20will%20not%20necessarily%20be%20in%20same%20row%2C%20which%20makes%20it%20difficult%20to%20accurately%20count%20the%20cells%20without%20hyperlinks%20for%20a%20particular%20person.%20I%20have%20attached%20a%20doc%20which%20shows%20it%20better%2C%20thanks%20and%20i%20hope%20you%20can%20help%20as%20i%20am%20stumped!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289509%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Philip%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20attached%26nbsp%3B%20the%20version%20that%20you%20can%20populate%20the%20formula%20via%20macrobutton.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289481%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289481%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jamil%3C%2FP%3E%3CP%3Ethanks%20for%20this%2C%20it%20works%20a%20treat.%20However%2C%20can%20i%20have%20it%20either%20to%20auto%20calculate%20or%20be%20ran%20from%20button%20click%20(Active%20X%20button).%20I%20have%20tried%20to%20link%20it%20to%20a%20button%2C%20but%20cant%20get%20it%20to%20run%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-285121%22%20slang%3D%22en-US%22%3ERe%3A%20COUNT%20CELLS%20THAT%20ARE%20NOT%20HYPERLINKED%20OR%20HAVE%20A%20CELL%20COLOR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-285121%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20the%20attached%20example.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20get%20what%20you%20need%2C%20by%20using%20UDF%20and%20COUNTA%20function.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20put%20as%20an%20example%20in%20T2%20this%26nbsp%3B%3DCOUNTA(B2%3AQ2)-HyperlinkFound(B2%3AQ2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20the%20UDF%20embedded%20in%20the%20workbook%20is%20this%3C%2FP%3E%0A%3CPRE%3EPublic%20Function%20HyperlinkFound(Rng%20As%20Range)%0A%20%20%20%20HyperlinkFound%20%3D%20Rng.Hyperlinks.Count%0AEnd%20Function%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
PHILIP ROCHESTER
Occasional Contributor

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

11 Replies
Solution

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

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

Hi Philip,

 

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

 

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! 

Hi Philip,

 

here is the modified UDF.

I have also embedded this into the workbook. you can run the macro.

 

Function myCountIf(rng As Range, criteria) As Long
 Counter = 0
For Each cell In rng.Cells
    If cell.Value = criteria And cell.Hyperlinks.Count = 0 Then
    Counter = Counter + 1
    End If
Next cell
myCountIf = Counter
End Function

Hi Jamil
you're a genius - i have tried numerous codes etc, but not been able to get this. (The only way i was able to get this was through creating a macro to sort into fill and no fill cells [for hyperlinks] and then use custom sort and functions for the names to get the totals - but this is too long a function and it slowed my doc down), yet what you have shown me .......this is exactly what i need. Thank you so much, you have helped me no end. I can now eventually finalise my doc, thanks to you 👍👍👍

Hi Philip,
You are very welcome and thanks for your kind words.

Hi Jamil

 

just one last thing - i need to have the calculation box (with the names and numbers on) and the macro button on a separate sheet (say, sheet 2). I have tried, but cant get it to work - could you help again please.

 

thanks in advance

 

Philip

Hi philip,

 

here it the attachment as per your latest request.

Perfect, thanks Jamil - seems so easy when i look at the code.

thanks again
Great! Thanks for the feedback.
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies