I want to count the cells with text

[wmbb]

I want to count the cells with text in red and black in an excel-sheet using the user defined function below:

Function FntColor(rng As Range) As String
If rng.Font.Color = RGB(0, 0, 0) Then
FntColor = "black"
ElseIf rng.Font.Color = RGB(255, 0, 0) Then
FntColor = "red"
Else: FntColor = "normal"
End If
End Function

What do I have to do to get it working in:
=COUNTIF(B5:B10,FntColor() = "black")

 

[SkipVought]

Your FontColor function requires an argument range reference. It has nothing to do with the COUNTIF function.

If you wanted to use the COUNTIF function, you would FIRST need to use the FontColor function in anothe column to return a string. THEN do a COUNTIF on THAT column to count string occurences of whatever.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wmbb]

Is it possible to do it all in VBA ?
Counting the cells in the range B5:C10 containing black text.

Suggestions for some code ?

 

[BrianWen]

Loop over the range and for each cell add 1 to a counter is the color is black...
There might be smarter ways, but this should work.

 

[wmbb]

Thnx for your response.

I'm no expert so I need some help....
What is the code to loop over the range B5:C10

Thnx in advance

 

[SkipVought]

Have you SEARCHED this site as suggested?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wmbb]

I think I missed the suggestion, but I always search the site first.

I didn't recognize anything I could try or use.
Do you know a link to post I could use ?

 

[BrianWen]

For Each cell In Range("B5:C10")

Next

 

[wmbb]

Thnx for your quick response

 

[BrianWen]

Could be like this:

Sub countblacktext()
  Dim counter As Integer
  counter = 0
  For Each cell In Range("A1:B2")
    If cell.Font.Color = 0 Then
      counter = counter + 1
    End If
  Next
  MsgBox ("There are " & counter & " cells with black text!")
End Sub

 

[xlbo]

For use on a spreadheet:

Sub countblacktext(rng as range)
  Dim counter As Integer
  counter = 0
  For Each cell In rng
    If cell.Font.Color = 0 Then
      counter = counter + 1
    End If
  Next
CountBlackText = counter
End Sub

Called using

=CountBlackText(B5:C10)

If you had done a search for "Sum by colour" as I suggested in your original MSOffice thread, you would've found something very similar - you could've just changed the sum to a counter...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wmbb]

Thanx for all your help.

 

[wmbb]

The code below is working for me.

Function act_saldo() As Integer
act_saldo = 0
For Each Cell In Range("B5:C10")
If Cell.Font.Color = RGB(0, 0, 0) Then
act_saldo = act_saldo + 1
End If
Next
End Function

I filled a cell with "=act_saldo()"
This results in the correct number of cells with black text.

But...
...when I change the font color of cells in the range, the value of the cell with the function does not change until the cell is selected and enter is pressed.
Is there a way to do this automatically.

(Automatic calculation is turned on !)

 

[SkipVought]

Add

Application.volatile

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}