Help with an IF statement


Well-known Member
Jun 4, 2008
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

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"


Well-known Member
Jun 4, 2008
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
End Sub

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics