vba 更改单元格中某些字符的颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7618121/
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
Change color of certain characters in a cell
提问by John Sheppard
I have the sentence "I would like 50 of those, please" in cell A1. I want to make any numeric characters red text (just the numeric characters). How do I do this? Here's the frame of what I have...
我在单元格 A1 中有一句“我想要 50 个，请”。我想让任何数字字符变成红色文本（只是数字字符）。我该怎么做呢？这是我所拥有的框架...
Sub RedText()
Dim i As Integer
For i = 1 To Len(Cells(1, 1).Value)
If IsNumeric(Mid(Cells(1, 1).Value, i, 1)) = True Then
'make the character red text
End If
Next
End Sub
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by aevanko
You can use the characters(start, length)property to do this. You can also store the text in a string and loop on that, which will be faster when you work with many cells. Here is an example:
您可以使用characters( start, length )属性来执行此操作。您还可以将文本存储在一个字符串中并在该字符串上循环，这在您处理多个单元格时会更快。下面是一个例子：
Sub RedText()
Dim i As Long
Dim text As String
text = Cells(1, 1).Value
For i = 1 To Len(text)
If IsNumeric(Mid$(text, i, 1)) = True Then
Cells(1, 1).Characters(i, 1).Font.Color = vbRed
End If
Next
End Sub
回答by brettdj
You can use a RegExp for the same effect.
您可以使用 RegExp 来达到相同的效果。
The advantage of the Regex approach being the code will isolate immediately any groups of numeric characters (or skip any strings that have no numerics) rather than testing each individual character.
Regex 方法的优点是代码将立即隔离任何数字字符组（或跳过任何没有数字的字符串），而不是测试每个单独的字符。
So it offers a speed advantage if you are processing reasonably large data sets
因此，如果您正在处理相当大的数据集，它会提供速度优势
Sub RegExpRed()
Dim objRegex As Object
Dim RegMC As Object
Dim RegM As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "\d+"
If .test(Cells(1, 1).Value) Then
Set RegMC = .Execute(Cells(1, 1).Value)
For Each RegM In RegMC
Cells(1, 1).Characters(RegM.FirstIndex + 1, RegM.Length).Font.Color = vbRed
Next
End If
End With
End Sub
