+ Reply to Thread
Results 1 to 10 of 10

(VBA) Check cells in sheet against cells above and to the right

  1. #1
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70

    (VBA) Check cells in sheet against cells above and to the right

    I have attached a small sample sheet, I have manually highlighted in yellow the cells that should end up in red.

    Here is the code I have tried to use (not sure it's even the right sort of procedures)

    Please Login or Register  to view this content.
    I'm getting a Compile Error "Next without For" - which doesn't make sense. It appears to highlighting "Next c"

    vb error 2.png

    vb error.png


    Any ideas?
    Attached Files Attached Files
    Last edited by AliGW; 11-11-2019 at 02:14 AM. Reason: Thread moved to VBA section.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007 Enterprise
    Posts
    4,844

    Re: (VBA) Check cells in sheet against cells above and to the right

    .
    Not certain what your macro does but it appears you were missing an END IF ...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70

    Re: (VBA) Check cells in sheet against cells above and to the right

    Well that clears the error - so thanks for that.


    The result is the inverse of what I want though - I only want the 2 yellow cells to turn red (they will not normally be yellow) as they are of a lower value than of the cells that are EITHER above them or to the right of them!

    vb error 3.png
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70

    Re: (VBA) Check cells in sheet against cells above and to the right

    Hmmm, I think a > should be a <, or vice versa.

    Can't test until tonight though.

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,837

    Re: (VBA) Check cells in sheet against cells above and to the right

    See attached

    This code highlights cell "M22" The two cells you have highlighted do not have a greater value above them or in one case to the right

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70
    Quote Originally Posted by nigelog View Post
    See attached

    This code highlights cell "M22" The two cells you have highlighted do not have a greater value above them or in one case to the right
    I'll check your code when I get home.

    However, the 2 cells highlighted do have greater values to the right.

    I'm not just interested in the cell immediately above or to the right, but if ANY cell above and to the right had a greater value.

  7. #7
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,837

    Re: (VBA) Check cells in sheet against cells above and to the right

    All of these cells are less than a cell to the right
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70

    Re: (VBA) Check cells in sheet against cells above and to the right

    Excellent, I'll look at your code when I get home, but in the face of it, I think you've cracked it.

    Thank you.

  9. #9
    Registered User
    Join Date
    09-28-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    70

    Re: (VBA) Check cells in sheet against cells above and to the right

    Yup - all sorted. It was the > in the the j loop that should have been a <.

    Thank you.

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    1,837

    Re: (VBA) Check cells in sheet against cells above and to the right

    Glad to help...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1