Highlight row if cell contains string from seprate user generated list

Tiny · Mar 22, 2016

Hi Folks

I'm looking for some help with 500K + lines of data over several workbooks, (and a buch of non-techie users).

After lots of searches I've found a few 'parts' that help, but have got myself confused with trying to put them together and the different ways of doing the same thing.

In a nutshell I want to enable users to put a list of keyword strings (list) specific to their needs into column A Sheet1 (Keywords)

Start a search were the strings in the list are used to search the 'data' (sheet2), where the column which will contain the keyword varies from workbook to workbook (not usually bigger column V).

If the string is found highlight the entire row. The user can then visually review the search hit (along with a few rows above and below if required).
{It woud be a bonus (but not required) if the actual hit row was one collour (red?) and ~6 rows either side were another colour (yellow?).}

Ultimately, my plan for the future is to set up a master copy sheet where users can paste into the 'data sheet' and add their search terms to the 'keywords' sheet, run the search and take away the results to study them.

I've adapted the below code below but his is as far as I've got before I blew my fuse

I seem to have got in an 'intersect' rut but I'm not sure is the right way to go.

Code:

Public Sub HighlightListedValues()
    Dim keywordList As String
    Dim Cell As Range

    'Creates a string concatenating your list of strings, separated by |s
    'e.g. "item1|item2|item3|item4|"
    For Each Cell In Sheets("keywords").Range("A2:A7") ' Needs to be variable!  [COLOR=#333333]?[FONT=Segoe UI]columns.Count, a.End(xlUp)?[/FONT][/COLOR]
        keywordList = keywordList & Cell.Value & "|"
    Next Cell

    'For each used cell in Column A of sheet1, check whether the value in that cell
    'is contained within the concatenated string
    For Each Cell In Intersect(Sheets("data").Range("H:H"), Sheets("data").UsedRange) 'H changes in other sheets
        If InStr(keywordList, Cell.Value) > 0 Then       'InStr returns 0 if the string isn't found
            Cell.EntireRow.Interior.Color = RGB(255, 0, 0) 'Highlights the row in red if value found
        End If
    Next Cell
End Sub

RCBricker · Mar 25, 2016

I sent you code in your email to test, but test Peter's as well. He has helped me wayyyy too many times for me not to recommend any solution I see of his.

Peter,

I sent him code that eliminated the use of "|" from the list and instead loaded the key words into an array and looped through those. I used the FIND function of excel to look in any column so that it did not need a column selected. The only issue I had with the sample data he sent me was one of the keywords showed up in a different column. I am awaiting his testing to determine if that was a false positive or not.

regards

Peter_SSs · Mar 25, 2016

It would appear that there has been some off-forum communication and data/code swapping. Both should read #4 of the Forum Rules.

There are many reasons for that rule, two of which are:

1. If the problem has been solved off-line, then other readers searching for a similar solution without posting their own question (that is the majority of the the forum's users) do not have access to that solution or how it was arrived at.

2. Members like myself who have invested considerable time developing, testing & posting a suggestion based on the public status of the thread are well less than pleased if it turns out the problem has already been solved, or the data, layout or requirements have actually changed but that information is not public, & hence time & effort may have been wasted.

RCBricker · Mar 25, 2016

Peter_SSs said:
It would appear that there has been some off-forum communication and data/code swapping. Both should read #4 of the Forum Rules.

There are many reasons for that rule, two of which are:

1. If the problem has been solved off-line, then other readers searching for a similar solution without posting their own question (that is the majority of the the forum's users) do not have access to that solution or how it was arrived at.

2. Members like myself who have invested considerable time developing, testing & posting a suggestion based on the public status of the thread are well less than pleased if it turns out the problem has already been solved, or the data, layout or requirements have actually changed but that information is not public, & hence time & effort may have been wasted.

Peter

The data swap was to check an error he was getting from the first code I wrote. I did not have his data to test against. You are correct, I will put my request for offline data swapping in the thread from now on.

That said, there did not seem to be anyone else interested as the OP and I were communicating here on our own for a bit.

I apologize if this caused you to waste any of your time working on this. Though your solution looks good and I did learn some stuff I am working on getting comfortable with from your code (as usual).

Regards,

Tiny · Mar 29, 2016

Gents
What can I say!!! apart from ..... thank you so much!!! your efforts are greatly appreciated.
I was following most of RCBrickers code, but when I tried Peters.... I struggled & had to look up some of the 'command'!

RCBricker
I've tested your code & it seems to work, ok with exact matches on smaller sheets, on a full size (200k lines+) sheet it seems to hang, so I can't tell. Am I asking too much, that it searches ALL columns in the work book?
Also, (not that it matters even slightly from my point of view), the line colours extend across the entire row, including the blanks after the data.
I like the little extra 'countif' alongside the keywords..... I was thinking of doing that as well as an assist/cross check.

 Peter_SSs 
You've cracked it!!...... After some working up, your code managed the 200k+ worksheet (the biggest single one I have at the moment) with 8 keywords in about 5 seconds, & I can't see any anomalies. I'm stunned.

Getting the user to select the column to search was the key, I reckon. I think this is the one we can usefully put to task.

I can completely accept that my 1st attempt at code was misleading, as I said, "I seem to be stuck in an 'intersect' rut but I'm not sure is the right way to go".

Thanks Guys.

Tiny · Mar 29, 2016

Peter

I can concur with RCBrickers' comments above, I sent him a sample data test sheet to work with. No offence intended.

If it helps is there a way I can post the sheet and associated code on the forum?

Apologies from me to.

Peter_SSs · Mar 29, 2016

Tiny said:
Peter_SSs
You've cracked it!!...... After some working up, your code managed the 200k+ worksheet (the biggest single one I have at the moment) with 8 keywords in about 5 seconds, & I can't see any anomalies. I'm stunned.

Good news.

Tiny said:
Getting the user to select the column to search was the key, I reckon.

I was pretty sure you only wanted to search one column (per workbook) since this from post #1 was written in the singular ". where the column which will contain the keyword varies from workbook to workbook ..". Similarly from post #6 "They will easily recognise the column they need to read from .."

The keys to the speed difference in the codes include that my code ..
- reads all the keywords and all the data to be checked into memory and processes it all there, rather than continually going back to the sheet to get the next value to check. vba code interacting with the sheet is one of the slower vba processes.
- checks each cell value for any of the keywords in a single test.
- temporarily sorts the data so that all the yellow rows are coloured in a single block & same for red then re-sorts into the original order. Although sorting is not always that fast, I think in this case it is much faster than separately colouring hundreds or thousands of disjoint blocks of rows.

Anyway, glad you ended up with something you can use.

Peter_SSs · Mar 29, 2016

Tiny said:
.. sample data test sheet to work with ..

The best ways are ..

1. Post a small set of sample data directly in your post. My signature block below has a link with more information about how to do that. There is similar information in the Forum Use Guidelines & a link to that is also included in my signature text.

2. If an actual workbook is required, it can be uploaded to a public file-share site and a link to that provided in a post here. There are some limitations with that in not all forum members choose to download such files and some are prevented from doing so by workplace security restrictions.

With both of the above methods, at least all readers know that there is data being shared and have the opportunity to try to access it if they want to.

RCBricker · Mar 29, 2016

Tiny said:
Gents
What can I say!!! apart from ..... thank you so much!!! your efforts are greatly appreciated.
I was following most of RCBrickers code, but when I tried Peters.... I struggled & had to look up some of the 'command'!

RCBricker
I've tested your code & it seems to work, ok with exact matches on smaller sheets, on a full size (200k lines+) sheet it seems to hang, so I can't tell. Am I asking too much, that it searches ALL columns in the work book?
Also, (not that it matters even slightly from my point of view), the line colours extend across the entire row, including the blanks after the data.
I like the little extra 'countif' alongside the keywords..... I was thinking of doing that as well as an assist/cross check.

[FONT=&][FONT=&] Peter_SSs [/FONT][/FONT]
You've cracked it!!...... After some working up, your code managed the 200k+ worksheet (the biggest single one I have at the moment) with 8 keywords in about 5 seconds, & I can't see any anomalies. I'm stunned.

Getting the user to select the column to search was the key, I reckon. I think this is the one we can usefully put to task.

I can completely accept that my 1st attempt at code was misleading, as I said, "I seem to be stuck in an 'intersect' rut but I'm not sure is the right way to go".

Thanks Guys.

See told you Peter was good.

regards

Tiny · Mar 30, 2016

Peter_SSs said:
Good news.
Anyway, glad you ended up with something you can use.

They guys who are using the end result are 'chuffed to bits' (apart from one who wants spoon feeding with a golden spoon.... you can't please them all

). & there are already some other uses for it being dreamt up

Tiny · Mar 30, 2016

RCBricker said:
See told you Peter was good.

regards

You're not kidding!!!...

Not forgetting your efforts, I've picked up a few good tips thanks

Highlight row if cell contains string from seprate user generated list

Tiny

Board Regular

RCBricker

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

RCBricker

Well-known Member

Tiny

Board Regular

Tiny

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

RCBricker

Well-known Member

Tiny

Board Regular

Tiny

Board Regular

Similar threads

Forum statistics

Share this page

Highlight row if cell contains string from seprate user generated list

Board Regular

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Board Regular

Board Regular

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock