Results 1 to 8 of 8

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

Threaded View

Alexander2020 Highlight the cells (verify)... 08-09-2020, 11:05 AM
Marc L Re: Highlight the cells... 08-09-2020, 11:29 AM
Marc L Avoiding the useless … 08-09-2020, 11:47 AM
Alexander2020 Re: Highlight the cells... 08-09-2020, 12:15 PM
Marc L Re: Highlight the cells... 08-09-2020, 07:21 PM
Greg M Re: Highlight the cells... 08-09-2020, 03:07 PM
Alexander2020 Re: Highlight the cells... 08-10-2020, 12:40 AM
Greg M Re: Highlight the cells... 08-10-2020, 03:10 AM
  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):

    Sub Verify_formulas()
        For Each cl In Worksheets("CARS").Range("B4:B" & Worksheets("CARS").Cells(Rows.Count, 2).End(xlUp).Row)
          If cl <> "" Then
    If Application.Sum(cl.Offset(, 1), cl.Offset(, 2), cl.Offset(, 3), cl.Offset(, 4), cl.Offset(, 5)) <> cl Then cl.Interior.ColorIndex = 3
          End If
        Next cl
    End Sub
    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

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