Help with an IF statement

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
729
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"

dchaney

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top