+ Reply to Thread
Results 1 to 8 of 8

Highlight the cells (verify) that give a SUM error on a column (specific range)

  1. #1
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    39

    Question Highlight the cells (verify) that give a SUM error on a column (specific range)

    I need to verify few formulas in my target workbooks. Each formula is a sum of few columns.
    I want to search and highlight the cell in the column of the total when its value is different than the sum of its components (other cells on the same row).
    Things are simple more or less when the sum is made only from few cells.

    I was able to make a code work for me from scratches (I attached a sample EXCEL file):

    Please Login or Register  to view this content.
    However I have situations of 20 cells to add. They are near each other (consecutive/adjacent) on the same row...
    ... should be a way to write the code in a shorter format, but I don't know how without writing 20 times the "cl.Offset" string.
    Please help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Highlight the cells (verify) that give a SUM error on a column (specific range)


    How a sum formula can be different than the 'sum of its components' ?‼

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Avoiding the useless …


    … just applying the correct formula to column B needing an unique VBA codeline according to your attachment :

    Range("B4", [B3].End(xlDown)).Formula = "=SUM(C4:" & [B4].End(xlToRight).Address(False, False) & ")"

    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  4. #4
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    39

    Re: Highlight the cells (verify) that give a SUM error on a column (specific range)

    The idea is to verify. I sent the workbook to many people to fill in a survey... The formulas were protected by password... but someone else from my company saw they were struggling to fill the survey and gave them the password. Some of them did some copy/paste values... and the formula has been modified in many places. I need to find were the value written in the SUM columns is different than the components to repair the formula before attempting to centralize the situations.

    Perhaps your line of code is good... but sorry... I am not happy with it... I do not understand it at all while I understand mine to modify as I need for other columns and worksheets.
    I think your code assumes that there are no other cells at the right end of the row (as in my sample of Excel), but in reality I always have many others.

    I've just asked for a shorter way to modify my code, instead of writing cl.Offset(, 1), cl.Offset(, 2), cl.Offset(, 3),..., cl.Offset(, 18), cl.Offset(, 19), cl.Offset(, 20)... to make that range shorter.
    I tried something like For i =1 to 20, Sum (cl.Offset(, i)) or Sum (cl.Offset(, 1): cl.Offset(, 20))... But it is not legal code.
    Last edited by Alexander2020; 08-09-2020 at 12:22 PM.

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Highlight the cells (verify) that give a SUM error on a column (specific range)

    Hi there,

    Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Highlight the cells (verify) that give a SUM error on a column (specific range)


    Quote Originally Posted by Alexander2020 View Post
    […] to repair the formula before attempting to centralize the situations.
    This is exactly the purpose of my single short codeline ! …

  7. #7
    Registered User
    Join Date
    05-29-2020
    Location
    Chisinau
    MS-Off Ver
    2019
    Posts
    39

    Re: Highlight the cells (verify) that give a SUM error on a column (specific range)

    Thank you guys!
    @Marc L.... I finally understood your idea... Correcting the formula instead of highlighting the error. Right... but sometimes you want to see where is the mistake before making the correction. Thank you very much anyway, cause indirectly you helped me understand the mistake in my definition of the range (down, right etc).
    @Greg M... your code does exactly what I asked... I see... I can setup the number of the columns to take in account! I'll mark the thread as solved!

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Highlight the cells (verify) that give a SUM error on a column (specific range)

    Hi again,

    Many thanks for your feedback and also for the Reputation increase - much appreciated!

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Highlight Row and Column on specific range only.
    By sickreto in forum Excel General
    Replies: 2
    Last Post: 03-30-2020, 04:55 PM
  2. Replies: 2
    Last Post: 04-17-2014, 03:30 PM
  3. [SOLVED] Cond. formatting a column/Range, highlight cell in column/range if equal to specific text
    By DFrank231 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 03:35 PM
  4. [SOLVED] Highlight a range of cells if a single cell contains specific text
    By ZHertz in forum Excel General
    Replies: 3
    Last Post: 10-19-2012, 02:54 PM
  5. [SOLVED] Highlight cells of a particular value or within a specific range
    By DJS22867 in forum Excel General
    Replies: 3
    Last Post: 06-29-2012, 10:11 AM
  6. Highlight doubleclicked cells of a specific range
    By taya in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2010, 04:40 PM
  7. highlight cells with values outside a specific range
    By excel_dumdum in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-21-2009, 09:40 PM

Tags for this Thread

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