'VBA to find string and change interior color
I need to find all instances of "available" and "only" in the same cell (always in column H)and change the interior color of that cell to yellow. Then I need the cells in the next 3 columns (columns I,J,K) in the same row to have interior color yellow also. The code below changes the cells in column H to yellow but I don't know how to also make the cells in columns I,J,K to be yellow.
Edited to add second part: This seems to work, however there could be a better way.
Sub MakeOnlyYellow()
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbYellow
Columns("H").Replace "*Available*only*", "", , , False, , False, True
Application.ReplaceFormat.Clear
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "H").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 8).Interior.Color = vbYellow Then
Cells(i, 9).Interior.Color = vbYellow
Cells(i, 10).Interior.Color = vbYellow
Cells(i, 11).Interior.Color = vbYellow
End If
Next
Application.ScreenUpdating = True
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
