Help with an IF statement

[dchaney]

Hello Again All

I am trying to learn more about VBA and I have been. I am working on an issue now that I am getting stuck on. Basically what I need to do is look at sheet1 and If any cell in column Q = "x" then go to Column A, look upward till you find the number 2 and highlight the row.

I have a hierarchal list of parts on sheet1 named BOM, listed from level 1 to level 7. Then I have another sheet named PID that contains a list of parts. On the BOM tab I have a vlookup to place an X in column Q for any part listed on the PID tab. The parts on the PID tab are normally level 3, 4, 5, etc... I need to identify the level 2 part that resides ABOVE them.
Any help would be wonderful, thank you

 

[dchaney]

I have gotten the code to locate all lines with "X" in column Q and highlight them, but I am still running into issues of figuring out how to go back up and find the previous line that contained a 2 in column A and highlight it

Sub BOM()
'
'
Dim i As Integer
Dim rng As Range
Dim lastRow As Long
Application.ScreenUpdating = False
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:R" & lastRow) 'Sets the range for the macro to operate within
  
    i = 2 'Starts the loop at row 2
      Do Until i = lastRow + 1 'Continues the loop untill the last row of information has been discovered
        With rng
            If Cells(i, 17).Value Like "X" Then 'Check to see if the info in the second column of the current row (i) containes X
                Range(Cells(i, 1), Cells(i, 18)).Interior.ColorIndex = 36 ' Color the row from column A to R tan
            End If
        End With
    i = i + 1
    Loop
End Sub