vba - Excel 根据上面的字符串应用颜色

Question

我目前在excel中有一个这样的消息线程:

Cell A1 Joe SmithSunday 16th December
Cell A2 Hey it's Joe
Cell A3 Jim BobSunday 16th December
Cell A4 Hi Jim!
Cell A5 Jim BobMonday 17th Decembver
Cell A6 How are you?
....

我想要做的是将 Joes 消息涂成红色，将 Jims 消息涂成蓝色。谁能帮我理解如何做到这一点？

我需要一些代码来循环我的工作表并尝试将文本与已知的起始字符串匹配，然后相应地更改其颜色。我不擅长 VBA，但这里有一些 sudo 代码:

    if string starts with "Joe Smith" turn text colour of row below red  
else  
    if string starts with "Jim Bob" turn text colour of row below blue    
else  
   skip

Answer 1

相当直截了当。这可能是您为消息着色的简单机制

Sub SimpleColoringMechanism()

    Dim c As Range

    ' loop through all cells in range A1:A + last userd Row in column A
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

        ' when Joe Smith is found in cell's content
        If InStr(1, c.Text, "Joe Smith", vbTextCompare) > 0 Then

            ' color the message in red
            c.Offset(1, 0).Font.Color = vbRed


        ' when Jim Bob is found in cell's content
        ElseIf InStr(1, c, "Jim Bob", vbTextCompare) > 0 Then

            ' color the message in blue
            c.Offset(1, 0).Font.Color = vbBlue

        End If
    Next

End Sub

这会为单元格中的文本着色

为整个单元格背景着色替换 c.Offset(1, 0).Font.Color = vbRed与 c.Offset(1, 0).Interior.Color = vbRed如果您想更改整个 ROW，只需添加 c.offset(1,0).EntireRow.Interior.Color