# Comment Cell When Row With Consecutive Column Has Same Entry

• Can anyone suggest a formula to automatically comment a cell when rows with given number consecutive columns has same entry example:

A B C D E F G H
1 0 0 0 0 5 6 7 18
2 0 0 5 6 7 0 8 26
3 0 5 0 0 0 0 0 5

row 1: there are minimum 4 '0' in consecutive columns, so H1 is sum of A1:G1 = 18 and automatically comment cell H1 "there are 4 zero"

row 2: because there are no 4 '0' in consecutive columns, H2 is 26 , and no comment

row 3: there are 5 '0' in consecutive columns, so H3 is 5 and automatically commented cell H3 "there are 5 zero"

• Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

I think a formula might be very complicated, and I think impossible as far as writing a comment is concerned. So I've gone down the vba route. Explore this (albeit amateur) offering:

The above code has to be manually run but it could be made to run on a worksheet change event if the specified range is changed.

In column H I just put the formula
=sum(A1:G1) in the first row and copied down.

At the very least it should give you some suggestions.

p45cal

• Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

Dear p45cal,

What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

• Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

Is it always Zeros?

• Re: Comment Cell When Row With Consecutive Column Has Same Entry

Hello,

It's not always.

I am still trying to find out how to do this without running it manually, ie: via function.

Somehow at times I keep getting #NAME?

*sigh*

• Re: Comment Cell When Row With Consecutive Column Has Same Entry

#NAME! means the formula is not being recognized by Excel

• Re: Comment Cell When Row With Consecutive Column Has Same Entry

Quote from ceruin

I am still trying to find out how to do this without running it manually, ie: via function.

You will not get a function that adds a comment to a cell. a function returns a result of one or more evaluations in a cell - that's it.

You will need a code or conditional formatting based solution to your problem - a function will not do what you want.

• Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

This in the worksheet code module for the sheet in question (where the flashing cursor is when you right click on the worksheet's tab and choose 'View Code...'

The
Range("\$A\$1:\$G\$3")
bit is the specified range mentioned below. No checks have been put in to check that only one row of cells has been changed; it's just for ideas.

As far as making a user defined function is concerned, the following basic UDF will give a result in a cell (not its comment):

Put this preferably in a standard code module.
It won't work for anything but a single contiguous row of cells with at least 2 cells, but you're looking for more than 3 consecutives anyway.

p45cal

Quote from ceruin

Dear p45cal,

What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!