VBA code for selecting a row based on text and using conditional formatting

[FrenchCelt]

Hello,

I'm trying to add a snippet of VBA code to a macro I've created. I want to select a row (the row # will vary, so I can't use that in the code) that contains a specific string of text from column A ("Total Unapproved Indirect Labor"), and then run conditional formatting to highlight cells greater than 29.99. I cobbled this together, but instead of highlighting the values in the row I want, it's highlighting the values in column B:

Last = Cells(Rows.Count, "A").End(xlUp).Row    For j = Last To 1 Step -1
        If (Cells(j, "A").Value) = "Total Unapproved Indirect Labor" Then
    'Cells(j, "A").EntireRow.Select
        End If
    Next j
    With Selection
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=29.99"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End With

Can anyone pinpoint where I went wrong and suggest a fix?

 

[mikerickson]

I notice in the posted code, that the line selecting the .EntireRow is commented out.

 

[Fluff]

If you uncomment this line

'Cells(j, "A").EntireRow.Select

It works for me.

 

[Fluff]

Another option would be

   Dim Fnd As Range
   Set Fnd = Range("A:A").Find("Total Unapproved Indirect Labor", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox "not found"
      Exit Sub
   End If
    With Fnd.EntireRow
      .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
         Formula1:="=29.99"
      .FormatConditions(.FormatConditions.Count).SetFirstPriority
      With .FormatConditions(1).Font
         .Color = -16383844
         .TintAndShade = 0
      End With
      With .FormatConditions(1).Interior
         .PatternColorIndex = xlAutomatic
         .Color = 13551615
         .TintAndShade = 0
      End With
      .FormatConditions(1).StopIfTrue = False
    End With
End Sub

 

[FrenchCelt]

I can't believe I missed the commenting out.

Thanks all, uncommenting was all it took to fix the problem.

 

[Fluff]

Glad we could help & thanks for the feedback