Highlighting Conditional Character Count

I have sent text to columns such as in A1 we have:

Hey my name is Bob and then it spreads each word out such that B1 = Hey, C1 = my, D1 = name and etc.

I then need to do a character count over the range of cells and I need to highlight the cell that goes over 10 characters.

So in this case, the cell with "name" in it will be highlighted since it goes over the character limit. It goes over the limit because Hey has 3 characters but I also need to account for a space, my has 2 + 1 space, and then name > 10. I need to do this to all of the cells in column A until A has no more data and over all of the worksheets.

Does that make sense? Can anyone help?

Hi BJ,

Try this:

Please Login or Register  to view this content.

I have 2 questions about the code:

I do not want the concatenated cell to be highlighted. I want the cell in the text to columns to be highlighted that violates character count

1)Can it be changed in a way as such: AG contains the concatenated text of W:AF and I want the cell that goes above 31 characters in between W and AF to be highlighted?

Just change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

to not highlight the concatenated cell.

Do you want to deconcatenate AG backwards?

And after changing it, nothing gets highlighted. Before only the concatenate cell was, now nothing is. I see why. You are just taking the character count of each cell. I want them to add up. So if cell A contains 7 characters and cell B contains 4 then cell B is highlighted because 7+4 = 11 >10. Does that make sense?

I am saying 10 and cells A and before for simplicity. Really character count limit is 31 and it starts in cell W:AF

I'll have to look at it again later.

Ok, I do appreciate your help on this. I think the code is close, just have to take into account the characters in each cell before the one you are looking at.
So basically I want to start counting characters in cell W going to AF. Each time I want to count characters and add one character for a space except last word because you don't need a space after that. If count goes over in cell Z, then Z is highlighted, if AD, then AD is highlighted. Only one cell should be highlighted per row because it should only reach the limit once.

Hi BJ,

Try this version:

Please Login or Register  to view this content.

See if it lines up now:

Please Login or Register  to view this content.