CODEWITHSUNDEEP
vbaexcel
J.Zil
J.Zil

Reputation: 2449

Excel apply colour based on string above

I have a message thread in excel like this currently:

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?
....

What I want to do is color Joes messages in red and Jims messages in blue. Can anyone help me to understand how to do this?

I need some code to loop through my sheet and try and match the text with a known starting string then change its colour accordingly. I'm not good with VBA but here is some sudo code:

    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

Upvotes: 1

Views: 1442

Answers (3)

user2140173
user2140173

Reputation:

Quite straight forward. This could be your simple mechanism for coloring the messages

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

This colors the text in cell

for coloring the entire cell background replace c.Offset(1, 0).Font.Color = vbRed with c.Offset(1, 0).Interior.Color = vbRed and just to add if you want the entire ROW changed add a c.offset(1,0).EntireRow.Interior.Color

Upvotes: 2

Mike
Mike

Reputation: 3811

This code is not tested, but it should get you at least a good idea of what you need to do.

Dim iterator as integer
Dim columnCount as integer

With ActiveSheet
    columnCount = UsedRange.Rows.Count

    For iterator = 1 to columnCount
        If .Cells(iterator, 1) Like "Joe Smith*" Then
            .Cells(iterator + 1, 1).Interior.ColorIndex = 3
        ElseIf .Cells(iterator, 1) Like "Jim Bob*" Then
            .Cells(iterator + 1, 1).Interior.ColorIndex = 4
        End If
    Next
End With

Supporting references here and here.

Upvotes: 1

Julien Marrec
Julien Marrec

Reputation: 11895

Go in cell A2, click on "Conditional Formatting" then "New rule" and select "Use a formula to determine which cells to format"

Enter

=FIND("Joe Smith",$A1)>0

Click on format and set Font > Color to blue

Redo the exact same thing on cell A2 and enter

=FIND("Jim Bob",$A1)>0

Click on format and set Font > Color to red

Then go to Conditional Formatting > Manage Rules and in "applies to" just put "$A:$A"

Upvotes: 2

Related Questions