vba 如何在excel中计算不同字体颜色的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15887257/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count up text of a different font colour in excel
提问by Tony Amofah
I have a list of names that has been exported from another database into excel. The names in the list that are of interest are highlighted in red font. I would like a way to count it, i.e. John Smith appears 5 times in total in a column but 3 of the 5 times, his name comes up highlighted in red font. So I would like to see how many instances of his name comes up red.
我有一个从另一个数据库导出到 excel 的名称列表。列表中感兴趣的名称以红色字体突出显示。我想要一种计算方法，即约翰史密斯在一列中总共出现了 5 次，但在 5 次中出现了 3 次，他的名字以红色字体突出显示。所以我想看看有多少他的名字出现红色。
I know How to search all instances of his name e.g. =COUNTIF(A1:A100,"John Smith ")
我知道如何搜索他名字的所有实例，例如 =COUNTIF(A1:A100,"John Smith ")
I've also had help in creating a VB function which counts all values that are red (=SumRed) (once the colour index is specified) in a worksheet by using this:
我还帮助创建了一个 VB 函数，该函数使用以下方法计算工作表中所有红色 (=SumRed) 值（一旦指定了颜色索引）：
Function SumRed(MyRange As Range)
SumRed = 0
For Each cell In MyRange
If cell.Font.Color = 255 Then
SumRed = SumRed + cell.Value
End If
Next cell
End Function
I just can't find a way to combine the two counting conditions. Any help would be much appreciated!
我只是找不到将两个计数条件结合起来的方法。任何帮助将非常感激！
回答by Siddharth Rout
You don't need VBA for this but still if you want VBA Solution then you can go with any of the other two answers. :)
您不需要为此使用 VBA，但是如果您想要 VBA 解决方案，那么您可以使用其他两个答案中的任何一个。:)
We can use Excel formula to find the Font Color of a cell. See this example.
我们可以使用 Excel 公式来查找单元格的字体颜色。请参阅此示例。
We will be using XL4 macros.
我们将使用 XL4 宏。
- Open the Name Manager
- Give a name. Say
FontColor - Type this formula in Refers To
=GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1))and click OK
- 打开名称管理器
- 给个名字。说
FontColor - 在引用中键入此公式，
=GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1))然后单击确定
Explanation of the formula
公式说明
The Syntax is
语法是
GET.CELL(type_num, reference)
Type_num is a number that specifies what type of cell information you want.
reference is the cell reference
In the above formula the number 24gives you the font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0. And Hence the drawback. Ensure that the entire font color is red. We could have used 64 but that is not working properly.
在上面的公式中，数字24为您提供了单元格中第一个字符的字体颜色，作为 1 到 56 范围内的数字。如果字体颜色是自动的，则返回 0。因此是缺点。确保整个字体颜色为红色。我们本可以使用 64，但它不能正常工作。
OFFSET(INDIRECT("RC",FALSE),0,-1)refers to the immediate cell on the left.
OFFSET(INDIRECT("RC",FALSE),0,-1)指的是左边的直接单元格。
Now enter this formula in a cell =IF(AND(Fontcolor=3,B1="John Smith"),1,0)and copy it down.
现在在单元格中输入此公式=IF(AND(Fontcolor=3,B1="John Smith"),1,0)并将其复制下来。
Note: The formula has to be entered on the Right of the cell which contains the Text.
注意：必须在包含文本的单元格的右侧输入公式。
Screentshot
截图
EDIT (10/12/2013)
编辑 (10/12/2013)
To count cells with specific backcolor see THISlink
要计算具有特定背景色的单元格，请参阅此链接
回答by glh
I think you're almost there but this deserves another function @user bet me to the punch line :(
我想你快到了，但这值得另一个功能@user 打赌我的妙语:(
Function CoundRedAndText(MyRange As Range, Mytext as string) as long
CoundRedAndText = 0
For Each cell In MyRange
If cell.Font.Color = 255 and cell.value like MyText Then
CoundRedAndText = CoundRedAndText + 1 'you had cell.value but dont know why?
End If
Next cell
End Function
Usage, =CountRedAndText(A1:A25, "John Smith")
用法， =CountRedAndText(A1:A25, "John Smith")
回答by user2140261
For Each cell In Range("A1:A100")
If cell.Font.Color = 255 And cell.Value = "John Smith" Then
myCount = myCount + 1
End If
Next
