Select Cells that contain specific text vba

[Vivelake]

Hi,

For work we often get a spreadsheet that for my use I need to filter and highlight certain columns.
For example one column has some cells that contain the word yes. I need to highlight every cell that says yes and also highlight another cell in that row.
So if row 5 has the word yes in column B both B5 and another cell in the row need to be highlighted (i.e. E5)
The document has a different amount of entries every week but usually around 1500 row.
I have tried a couple of different ways but I'm new to VBA.

 

[offthelip]

welcome to the forum, try this code:

Sub testr()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
Range(Cells(1, 2), Cells(lastrow, 2)).Interior.Color = xlNone 'remove existing colors
Range(Cells(1, 5), Cells(lastrow, 5)).Interior.Color = xlNone
For i = 1 To lastrow
 If inarr(i, 1) = "yes" Then
    Range(Cells(i, 2), Cells(i, 2)).Interior.Color = vbRed
    Range(Cells(i, 5), Cells(i, 5)).Interior.Color = vbRed
 End If
 Next i
 End Sub

 

[Joe4]

Welcome to the Board!

You should be able to just use Conditional Formatting to do this.
Just select columns B and E, and then use this Conditional Formatting formula:

=$B1="Yes"

and then choose your desired highlighting color.