Results 1 to 8 of 8

Thread: Highlight Sequential Cells That Sum Up To A Value

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14

    Highlight Sequential Cells That Sum Up To A Value

    Hi Firend,

    I need macro which has to highlight cells in the particular column up to the extent of the given value.

    The excel sheet has around the 30-50 K rows many columns with value.

    If I want to pick up the value cell which is equal to 10 laksh then it should highlight the cells which total comes to 10 lakhs.

    In case sum of the cells not come exactly to 10 laksh then it should show very near to 10 lakhs.

    I have attached the sample date for your reference.

    Plz do the needful.

    Prabhu.
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Why don't you try conditional formatting....

    =SUM($K$2:$K2)<$L$1 dragged down till the last row

    See attachment
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Quote Originally Posted by Excel Fox View Post
    Why don't you try conditional formatting....

    =SUM($K$2:$K2)<$L$1 dragged down till the last row

    See attachment

    Hi Admin,

    This formula is considering from first cell till near by value.If the value is exceed the value then it shows as false.

    Attached workbooks shows by applying this formula we can get the value of Rs.929383 but if we find manually we can get exact value of 10 L(Highlighted in Green).

    So we need to check entire cells in the particular column and find out very near by value.

    Is there any way to do so(By formula or Macro)?

    Plz help.
    Attached Files Attached Files

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    Sub snb()
        j = 1
        Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
            j = j + 1
        Loop
        Cells(2, 13).Resize(j - 1).Interior.ColorIndex = 4
    End Sub

  5. #5
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14
    Quote Originally Posted by snb View Post
    Code:
    Sub snb()
        j = 1
        Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
            j = j + 1
        Loop
        Cells(2, 13).Resize(j - 1).Interior.ColorIndex = 4
    End Sub
    Hi,

    Same result am getting.

    It is not hithlighting very near to 10 L.

    Plz fidn the attached working which i have highlighted the cells(Manualy) which is very near to 10 L.

    Kindly do the needful.

    Prabhu
    Attached Files Attached Files

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    You must be kidding; it does exactly what you were asking for.

    I fear you have no idea what the code is doing

    You can easily adapt it to:

    Code:
    Sub snb()
      j = 1
      Do Until Application.Sum(Cells(2, 11).Resize(j)) >= Cells(1, 12).Value
        j = j + 1
      Loop
      Cells(j, 11).Interior.ColorIndex = 4
    End Sub
    Last edited by snb; 06-24-2012 at 01:10 AM.

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Why don't you try =SUM($K$2:$K2)<=$L$1 instead?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Highlight Current Row in Excel (VBA)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 16
    Last Post: 07-31-2013, 06:46 AM
  2. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  3. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  4. Highlight Active Cell’s Row and Column
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  5. Replies: 1
    Last Post: 03-19-2013, 03:35 PM

Posting Permissions

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