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!

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

… 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 » ! ◄ ◄

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

Originally Posted by Alexander2020

[…] to repair the formula before attempting to centralize the situations.

This is exactly the purpose of my single short codeline ! …

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!

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