Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 22nd, 2007, 07:27 AM
Registered User
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Comparing the figures in two columns on each sheet

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.

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


' 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)


                        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
    Next Sheetx

    MsgBox "Complete"

Exit Sub


End Sub

Old February 22nd, 2007, 05:33 PM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

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.

Old February 26th, 2007, 12:55 PM
Registered User
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts

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
Old February 26th, 2007, 01:29 PM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Workbook,Copy Sheet,Move Sheet, Close/Save ptrussell2009 Excel VBA 0 June 13th, 2008 02:28 PM
access function in data sheet(another sheet) jani Excel VBA 1 May 21st, 2008 07:15 PM
Numerals into figures renu2214 Classic ASP Professional 0 April 17th, 2007 09:34 AM
Comparing two datasets vip1976 ADO.NET 1 December 2nd, 2005 01:42 AM
searching through a sheeft for minus figures Mark_Harrison Excel VBA 2 September 11th, 2003 03:39 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.