VBA to Conditional Format based on cell text

[Jefe187]

I know this is an easy solution using conditional format, but rows can change daily in my spreadsheet, columns however will stay the same. If the word "yes" is in column "J" then I would like to format that cell plus the next 6 to right. Example: J6 contains yes, then J6:P6 will fill blue, white lettering, bold. Here is what I have thus far.

Dim rngA As Range
Dim cell As Range

Set rngA = Range("J1", Range("J10000").End(xlUp))
For Each cell In rngA
If cell.Value = "Yes" Then
Range("J:P").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
End If
Next cell

End Sub

 

[Jefe187]

A bit cumbersome, but I got it to work.

LastRow = Cells(Rows.Count, "J").End(xlUp).Row


For i = LastRow To 1 Step -1
If Range("J" & i).Value = "Yes" Then
Range("J" & i).Columns("A:G").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
End If
Next i




End Sub