Forum Discussion
COUNT CELLS THAT ARE NOT HYPERLINKED OR HAVE A CELL COLOR
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 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.