Highlight row based on text within string in cell

**~TaC~** · 08-16-2019, 09:40 AM

Hi,

I have a spreadsheet where text in column I contains comments. In these comments I have reference numbers which are sometimes entered incorrectly.

Comments may include:
TCN95384 A4891644922
A4884103393 ABANDONED IN STORE
ABANDONED AT DC A4864666884
A4655177099 no carrier number

The reference starting with 'A' always has 10 numbers following it as can be seen.

Is there a way to highlight the row if the reference has more/less than 10 numbers to highlight the error?

Duplicate post
https://www.excelforum.com/excel-for...highlight.html
as I was looking to solve this with formula so I can use conditional formatting, if anyone can do this then I'd be interested to see how it works in my situation

Thanks

**MickG** · 08-16-2019, 11:45 AM

Try this:-

Please Login or Register  to view this content.

Regards Mick

**~TaC~** · 08-16-2019, 12:37 PM

Thanks, that seems to work great.

I changed
Dn.Interior.Color = vbYellow to
Dn.EntireRow.Interior.ColorIndex = 3 to highlight entire row.

I also changed "A" & <>10 to "TCN" & <>5 to get the same method working with the TCN reference but it didn't work.

Please Login or Register  to view this content.

Does something else need changing?

**MickG** · 08-16-2019, 01:28 PM

Try this:-

Please Login or Register  to view this content.

Regards Mick

**Rick Rothstein** · 08-16-2019, 01:33 PM

Here is another macro that you can try (change what's highlighted in red to actual data range's column letter and starting row number)...

Please Login or Register  to view this content.

For your second question, try this variation...

Please Login or Register  to view this content.

**Pete_UK** · 08-16-2019, 01:35 PM

Hi Rick,

I've not seen you posting on here for ages - welcome back …

Pete

**Rick Rothstein** · 08-16-2019, 01:40 PM

Originally Posted by Pete_UK

Hi Rick,
I've not seen you posting on here for ages - welcome back …

Thanks Pete! I am surprised that someone remembers me as I do not have a lot of posting in this forum. The MrExcel forum is my main place, but an online search for something I was looking up brought me back to this forum, so in my spare time, I have started revisiting here.

**Pete_UK** · 08-16-2019, 01:42 PM

I remember you most from the Code Cage and the old Newsgroups - some of your one-liners were amazing.

Pete

**Rick Rothstein** · 08-16-2019, 01:45 PM

Originally Posted by Pete_UK

I remember you most from the Code Cage and the old Newsgroups - some of your one-liners were amazing.

Ah, the old newsgroups... I still miss them. I still post one-liners and minimalistic codes over in the MrExcel forum... and I'll post them here if the question lends itself to that kind of solution.

**jason.b75** · 08-16-2019, 02:41 PM

When I said that it would probably need vba that was not an invitation to break forum rules and start another thread.

Duplicate of https://www.excelforum.com/excel-for...highlight.html

Reported to moderators for decision.

As per rule 6, this thread should receive no further replies until this issue has been addressed by a moderator.

**FlameRetired** · 08-16-2019, 02:51 PM

To confirm what jason said:

Edited mistakenly said X-posting.

Should be duplicate thread.

Thank you jason for correcting me. Post #12 below.

**jason.b75** · 08-16-2019, 03:02 PM

Dave, just to confirm, I was flagging this as a duplicate of another thread here, not a cross post.

If it has been cross posted then that makes 3 rules broken in this thread (missing code tags in post #2).

No doubt the OP will say that they were not aware of the rules

Ignorantia juris non excusat

**FlameRetired** · 08-16-2019, 03:02 PM

Pease see your post #3.

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**FlameRetired** · 08-16-2019, 03:03 PM

jason

Thank you. Will follow up.

**~TaC~** · 08-16-2019, 03:41 PM

Thanks Mick & Rick, suggestions work great!

**MickG** · 08-17-2019, 06:38 AM

You're welcome

**~TaC~** · 08-19-2019, 04:05 AM

Hi,

Sorry to drop back on this one but my project keeps evolving.

How would I adapt the following code thanks to MickG to only highlight columns A:L in a row rather than .EntireRow

Please Login or Register  to view this content.

Regards
Tez

**AliGW** · 08-19-2019, 04:09 AM

You might want to remove the SOLVED tag ...

**~TaC~** · 08-19-2019, 04:18 AM

Haha yeah thats a fair point... thanks

**MickG** · 08-19-2019, 07:42 AM

Try this:-

Please Login or Register  to view this content.

Regards Mick

**~TaC~** · 08-19-2019, 05:48 PM

I am so sorry, I now realise I never stated what column I am referencing for the lookup.

Column L is where the data is and starts row 6, in your previous code changing A2 to L6 had the same result in EntireRow colour change.
Now with your new code I change to L6 and it changes L onward.

However I have learnt something though so not an entire waste of time. I see that (, 12) changes the number of columns affected.

So, reference numbers are in column L and I would like to colour change A:L

Again sorry for wasting your time.

Tez

Highlight row based on text within string in cell

LinkBack

Thread Tools

Rate This Thread

Display

Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Re: Highlight row based on text within string in cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to highlight a specific cell based on value/text/number

How to highlight the cell based on text

Conditional Formatting to highlight cell based on values in a string

How to highlight a row in a color based on text in a cell on that row?

Attempting to Highlight Cells Based on String within Cell

[SOLVED] Highlight row based on character/text string containing in cell.

[SOLVED] Highlight a portion of string text (the string text is a value of a formula)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions