Tiny
Board Regular
- Joined
- Jan 21, 2008
- Messages
- 72
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.
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
Last edited: