我有一个多步骤的问题,我正试图用Google Sheets或Excel来解决。
首先,我有一张表,列出了所有的名称和分配的类,我需要找到类名的出现,并将每个名称放在类名旁边的一个单元格中。
然后,在另一张纸上,我把每个名字都链接到四种颜色中的一种。然后,我需要从这张表中提取匹配的颜色,并将其应用于单元格中名称的每个实例。
例如。
工作表1
Column A Column B Column C Column D
This kid Class A Class C Class B
That kid Class C Class D Class H
Your kid Class B Class A Class E
Other kid Class E Class F Class A
工作表2
This kid (in green text)
That kid (in blue text)
Your kid (in orange text)
Other kid (in red text)
工作表3
Column A Column B
Class A This kid; That kid; Other kid
Class B Other kid; Your kid; That kid; This kid
最终产品
Column A Column B
Class A This kid (green text); That kid (blue text); Other kid (red text)
Class B Other kid (red text); Your kid (orange text); That kid (blue text); This kid (green text)
我已经为此奋斗了几年,如果能得到任何帮助,我将不胜感激。
这是为了改变单元格中字符串的一部分的颜色:
c.Characters(Start:=iFrom, Length:=iLen).Font.Color = vbRed
其中c是单个小区的范围。你可以这样使用它:
Sub ChangeColor(c As Range, sWhat As String, iColor As Long)
If c.Count > 1 Then Exit Sub ' error
Dim iFrom As Long, iLen As Long
iFrom = InStr(1, c.Value, sWhat)
If iFrom > 0 Then
c.Characters(Start:=iFrom, Length:=Len(sWhat)).Font.Color = iColor
End If
End Sub
您可以通过以下方式检查单元格是否包含特定颜色:
Function IsColored(c As Range, iColor As Long) As Boolean
IsColored = False ' default
If c.Count > 1 Then Exit Function ' error
Dim i As Long, iLen As Long
iLen = Len(Trim(c.Value))
For i = 1 To iLen
If c.Characters(Start:=i, Length:=1).Font.Color = iColor Then
IsColored = True
Exit Function
End If
Next
End Function
请注意,这是对单元格值进行操作,而不是对字符串进行操作。