基于背景颜色计算excel中的文本实例 [英] Count instances of text in excel based on background color
问题描述
要求是这些代码可以根据是否存在于彩色单元格中的字符串ie(ABC,DEF,GHK)的实例进行计数,并将结果放在如下所示的单元格中。
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 .
任何人都可以建议?
我尝试了一个示例代码
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.
我也尝试过excel公式,但是我只能搜索文本和计数,它不会根据单元格的背景颜色进行计数。
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.
推荐答案
这是一个简单的用户定义函数。您可以将其放在常规模块中。您可以从工作簿中的任何工作表中调用它:
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:
在列E中,公式是:
=CountByColorAndText($A$2:$A$13,$D3,FALSE)
在列F中是一样的除了最后一个参数, CountColored
是 TRUE
。
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.
这篇关于基于背景颜色计算excel中的文本实例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!