Excel中文本字符串的格式列表 [英] Formatting List of Text Strings in Excel

问题描述

我正在尝试将字体变为红色，以便在excel中列出单词列表。到目前为止，我可以找到一个单词，但是我需要搜索一个整个数组。我是VBA的新手，挣扎着。到目前为止，我已经能够找到一个解决方案，但它涉及到找到一个单一的字符串F1：

I am trying to turn the font to red for the occurrences of a list of words in excel. So far, I am able to find a single word, but I need to search for a whole array. I am a newbie with VBA and struggling. So far, I've been able to find this as a solution, but it deals with finding a single string, "F1":

Sub test4String2color()
Dim strTest As String
Dim strLen As Integer
strTest = Range("F1")
For Each cell In Range("A1:D100")
If InStr(cell, strTest) > 0 Then
cell.Characters(InStr(cell, strTest), strLen).Font.Color = vbRed
End If
Next
End Sub

编辑：

我需要突出显示的单元格以逗号分隔列出格式。例如，苹果1，苹果3，香蕉4，橙。要搜索的值列表在不同的单元格中，苹果，香蕉4。我只想强调香蕉4，因为这是与逗号分隔值的EXACT匹配。在目前的形式中，Apple 1或Apple 4的文字将被部分突出显示。

The cells I need highlighted have the items listed in comma separated format. For example, "Apple 1, Apple 3, Banana 4, Orange". The list of values to search from are in Different cells, "Apple", "Banana 4". I only want to highlight "Banana 4" because this is an EXACT match with the comma separated values. In the current formulation, the text that says "Apple 1" or "Apple 4" would be partially highlighted.

编辑2：

这是我工作簿中的实际格式：

This is the actual format from my workbook:

推荐答案

这是通过循环遍历范围，集合和数组来实现所需要的方法。

代码将在集合（您选择的匹配字）和数组（每个单元格中分隔的字符串）之间找到匹配项。如果找到匹配项，字符串中的起始和终止字符将被设置，并且这些值之间的字符将被着色。

The code will find matches between the collection (your chosen match words) and the array (the string of words delimited in each cell). If a match is found, the starting and ending characters in the string are set and the characters between those values are colored.

Sub ColorMatchingString()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)
    Dim strTest As Collection: Set strTest = New Collection
    Dim udRange As Range: Set udRange = ws.Range("AC2:AC311") 'Define Search Ranges
    Dim myCell, myMatch, myString, i
    Dim temp() As String, tempLength As Integer, stringLength As Integer
    Dim startLength as Integer

    For Each myMatch In udRange 'Build the collection with Search Range Values
        strTest.Add myMatch.Value
    Next myMatch

    For Each myCell In ws.Range("A2:AB1125") 'Loop through each cell in range
        temp() = Split(myCell.Text, ", ") 'define our temp array as "," delimited
        startLength = 0
        stringLength = 0

        For i = 0 To UBound(temp) 'Loop through each item in temp array
            tempLength = Len(temp(i))
            stringLength = stringLength + tempLength + 2

            For Each myString In strTest
  'Below compares the temp array value to the collection value. If matched, color red.
                If StrComp(temp(i), myString, vbTextCompare) = 0 Then 
                    startLength = stringLength - tempLength - 1
                    myCell.Characters(startLength, tempLength).Font.Color = vbRed
                End If
            Next myString
        Next i
        Erase temp 'Always clear your array when it's defined in a loop
    Next myCell
End Sub

这篇关于Excel中文本字符串的格式列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！