Hi I have a problem

I am tring to compare two columns of numbers on each sheet in my workbook. I need to compare the Quantity in the first column with the MinMax level in another column for each row of data. And highlight the cell in red if the Quantity is less than or equal to the MinMax level.

Here is my code below (I have highlighted the point where I believe the if statements etc should be inserted.

Code:
Private Sub Extrapolate()

    On Error GoTo EndLine

    CataloguePageNo = "A"
    PartNumber = "B"
    TotalStock = "C"
    FrozenIndicator = "D"
    Description001 = "E"


    ProductCode = "A"
    Description = "B"
    PrintDate = "C"
    Quantity = "D"

    Dim Sheetx As Object

    For Each Sheetx In Worksheets

        'Stops the live data sheet being overwritten with data

        If Sheetx.Name = ActiveSheet.Name Then GoTo NextLine

'Stops the Min max levels sheet being overwritten with data

        If Sheetx.Name = "Min max levels" Then GoTo NextLine

        Sheetx.Range("A:G").ClearContents

' Adds column headings to each sheet in workbook

        Sheetx.Range("A1").Value = Range("G2").Value
        Sheetx.Range("B1").Value = Range("M2").Value
        Sheetx.Range("C1").Value = Range("H2").Value
        Sheetx.Range("D1").Value = Range("I2").Value
        Sheetx.Range("E1").Value = Range("J2").Value
        Sheetx.Range("F1").Value = Range("K2").Value
        Sheetx.Range("G1").Value = Range("L2").Value
        EntryRow = 2

        LastRow = Range(PartNumber & 1).End(xlDown).Row
        If LastRow = 65536 Then LastRow = 1

' Runs a loop that checks which CataloguePageNo(Family Group) is on that record of data on the first Live Data sheet(MS Query data)
' and selects the relevant sheet with that CataloguePageNo(Family Group) name

        For a = 2 To LastRow
            Select Case Val(Range(CataloguePageNo & a).Value)
                Case Val(Right(Sheetx.Name, 3))

' If the PartNumber has a print date on the end then split the date from the rest of the PartNumber and assign to new varibles

                    If Right(Range(PartNumber & a).Value, 9) Like "(####/##)" Then

                        ProdCode = Mid(Range(PartNumber & a).Value, 4, Len(Range(PartNumber & a).Value) - 12)
                        PDate = Mid(Right(Range(PartNumber & a).Value, 9), 2, 7)

                    Else

                        ProdCode = Mid(Range(PartNumber & a).Value, 4)
                        PDate = Empty

                    End If

                    ' add values from MS Query data columns to columns on each on stock check sheets

                    Sheetx.Range(ProductCode & EntryRow).Value = ProdCode
                    Sheetx.Range(PrintDate & EntryRow).Value = PDate
                    Sheetx.Range(Quantity & EntryRow).Value = Range(TotalStock & a).Value
                    Sheetx.Range(Description & EntryRow).Value = Range(Description001 & a).Value

                    If Sheetx.Range("D").Value = Sheetx.Range("H:H").Value Then

                    EntryRow = EntryRow + 1

                Case Else
            End Select

        Next a
NextLine:
    Next Sheetx

    MsgBox "Complete"

Exit Sub

EndLine:

End Sub

Not sure what you are trying to accomplish. Is there a lookup you need to do in H:H that compares to value in column D of the given row on the same spreadsheet?

If I am understanding this correctly and you are just comparing value from column D with value in column H it would be straightforward:

-----------------------------------------------------------
If sheetx.range("D" & EntryRow).value <= sheetx.range("H" & EntryRow).value then sheetx.rows(EntryRow).color = rgb(255,0,0)
-----------------------------------------------------------

If it is with any value in column H on sheet X you would have to iterate through each value, although if that is the case you should first find the highest value in that column since that will be the only <= comparison you'd need to do.

Hope this helped some.

I have entered the code bit below that you advised which seems to do excatly what I was looking for. I have inserted the IF statement into the code where I have lighted in the code in my first post.

When I run the code it does not finish and stops on the first row of data on the first sheet.

------------------------------------------------------------------------------------------------------------------------------------------
If sheetx.range("D" & EntryRow).value <= sheetx.range("H" & EntryRow).value then sheetx.rows(EntryRow).color = rgb(255,0,0)
------------------------------------------------------------------------------------------------------------------------------------------

I would grateful for any help.

Cheers Simon

Oops! I'm so sorry. I typed that in wrong. The correct line would be:

-----------------------------------------------------------
If sheetx.Range("D" & EntryRow).Value <= sheetx.Range("H" & EntryRow).Value Then sheetx.Rows(EntryRow).Interior.Color = RGB(255, 0, 0)
-----------------------------------------------------------

Notice that for some reason when I retyped it into the message I omitted the "Interior" part. Just insert ".Interior" between .rows(EntryRow) and .Color

Sorry for the inconvenience. Hope this works better.