Count instances of text in excel based on backgrou

问题:

The requirement is a piece of code which can count instances of a string i.e(ABC,DEF,GHK), based on if they are present in a colored cell or not and put the results in a cell below as shown .

Can anyone please advise?

I tried a sample code

Sub Color()

Dim varCounter As String
Dim color As Integer
Dim nocolor As Integer

Range("E5").Select
color= 0
nocolor= 0

      Do Until Selection.Value = ""
                  If Selection.Font.Color = RGB(255, 0, 0) Then
                   color= color+ 1
                     Else
                   nocolor= nocolor+ 1
                   End If
                   Selection.Offset(1, 0).Select
        Loop
  Range("E47").Select
  Selection.Value = no


  Range("E48").Select
Selection.Value = color

End Sub

This a very simple code which check if the text font is colored or not , buut I couldn't find anything which check for the background color of the cell.

I also tried excel formula , but that with that I can only search for text and count , it doesn't count based on background color of the cell.

回答1:

Here's a simple user-defined function. You can put it in a regular module. You can then call it from any worksheet in the workbook it's in:

Public Function CountByColorAndText(rng As Excel.Range, SearchText As String, CountColored As Boolean) As Long
Dim cell As Excel.Range
Dim CellCount As Long

For Each cell In rng
    If cell.Value = SearchText Then
         If (cell.Interior.ColorIndex = -4142 And Not CountColored) Or _
           (cell.Interior.ColorIndex <> -4142 And CountColored) Then
            CellCount = CellCount + 1
        End If
    End If
Next cell
CountByColorAndText = CellCount
End Function

It takes three arguments: the range to evaluate, the string to search for and whether you're counting colored (or uncolored) cells:

So, above, in column E the formula is:

=CountByColorAndText($A$2:$A$13,$D3,FALSE)

In column F it's the same except the last argument, CountColored is TRUE.

I don't write many user-defined functions, so somebody may come along and point out problems or improvements.

回答2:

Instead of Font.Color use Interior.Color

回答3:

Writing a code again would be great. But I have A REVISED CODE here one if you are interested to take a look.. I am just wondering if this is some commmon project though, coz yesterday the OP asks the very same question....

VBA, COUNTIF, Exclude based on cell color

BTW INTERIOR.COLOR will show you a big number that's a representation of RGB, where as you might want to use INTERIOR.COLORINDEX

Since you are checking RGB format, you may try the following. However I recommend you to not to use select, it slows down your code. You may change sheets, ranges according to yours needs.

e.g.

Dim rng as Range
Dim cell as Range

'-- name column
Set rng = Sheets(2).Cells(Sheets(2).Rows.Count, "C").End(xlUp).Row

color= 0
nocolor= 0

 For Each Cell In rng
    If  Cell.InteriorColor = RGB(256,0,0) then
      color= color+ 1
    Else
      nocolor= nocolor+ 1
    End If
  Next Cell

'--output
Sheets(2).Range("E47").Value = nocolor
Sheets(2).Range("E48").Value = color