I would like to develop a macro that can be run against an excel doc that will highlight keywords from a list I have established.
For example: If my excel doc contains the words, "sales, selling, sell, sale, school," make those words bold, red and 2 font sizes larger.
I perform a =Find(word,range) for certain keywords in text strings. Is there an easy way to have excel highlight the words within the string so i can easily identify its location? The text string might contain more than one keyword.
I have a list of Products in my Excel file, I am trying to put them into categories easily. So what I am asking is there a way to select all rows with a certain key word in them so I can easily paste them into my other excel file?
View 1 Replies View RelatedI need to find 100 or so keywords in a column, I have the list but how would I search multiple keywords as there are 10k columns and it would take forver to search for them manually?
View 4 Replies View RelatedI need to setup a macro that will search for a keyword in a list with about 21000 rows. When the macro finds the keyword it cuts the row out and moves it to another sheet. I'm having a few problems
1. I don't know how to make the function search until it can't find the keyword anymore, then move on to the next word
2. I can't get it to select what it finds it finds the cell but I don't know how to select that cell once its found.
Sub Search()
'
' Search Macro
'
Dim keyword As String
Dim x, y, z As Long
For x = 1 To 53
Sheets("Test1").Select
Range("A" & x).Select
keyword = Selection.........
I have rows of 10 keywords. I want the macros to compare the first row against all the rows except itself, then compare the second row against all the rows except itself, the third row against all the rows except itself, basically looking for duplicates...It will then record in a column for each respective row, the max # of duplicates found with another row..
For instance lets say I was looking at the 15th row, and it looked for duplicates elsewhere. It only found duplicates in the 4th row and the 5th row. In the fourth row, there were 5 keywords common, and in the 5th row there were 7 keywords common. Therefore the max # of common words is 7 for the 15th row, and that is what is recorded.
In addition the number of rows will probably vary it could range from 1 to 500. I included an excel spreadsheet that has visually speaking how this could work, maybe using COUNTIF. Basically it counts the # of common keywords for one row with all the other rows, pastes the count #s to the right, then finds the max of those count #s, copies the max into another column, then clears the count #s, and looks up the next row, repeats the same process. It is easier probably if you look at the attached spreadsheet to get an idea of what i am talking about..
Say I have a list:
VB:
Something something apple
Something something orange
Something something banana
Something apple something
Something banana something
Apples woo
Apples And bananas
Something orange something
Something something apple pie
Something something vegetables
And I want to separate the list into separate columns/lists with certain keywords (e.g. apple, orange, banana) like so:
Apple
Something something apple
Something apple something
Apples woo
Something something apple pie
[Code] .....
The "multiple words found" part isn't a big deal (I'm not sure how I'd deal with it anyways). The "Unsorted" part is there assuming creating the separate lists won't remove the entries from the original list (which would then leave all the unsorted text).
I have two Spreadsheets, lets call it Spreadsheet 1 and Spreadsheet 2.
Spreadsheet 1 has a list of partial employee names in column A.
Spreadsheet 2 has a list of 500,0000 full employee names in column A and their phone number in column B and address in column C through E.
Basically what I want to do is do a wildcard search for the partial employee name, so like lookup Spreadsheet 1' Column A's *name* in column A of Spreadsheet 2. Based on the number of matches. I want to insert that many blank rows underneath the partial employee name, and paste those matched rows there, then continue to the second partial employee name lookup and repeat the process.
This loop has to repeat 1000 times.
I have group of data that includes a list of names. What I'm trying to do is create a conditional format to highlight the row if the name is different from the name above it.
I couldn't figure out how to "highlight" my text or the rows, so I changed text-color. But what I'm looking for is a way to have the rows change color based on the names in the list.
I know how to get every-other-row to highlight, but I have names that repeat, and it's not consistent.
Blue Row
Adam Smith
Blue Row
Blue Row
Adam Smith
Blue Row
Red Row
Joan Allen
Red Row
[code]....
How do i highlight from given cell (A3) to end the of the list in that column without giving a specific range?
View 5 Replies View RelatedIs there a way I can compare the names in column A to those in both B&C and highlight them a certain color if they are the same?
View 3 Replies View RelatedWithin conditional formatting I use =COUNTIF(Task,I4)=0 and it highlights the non-matches. Task is a named range which works fine, but how could I actually just use a string instead of the named range? The named range only consists of Major and Sub. My first guess would be =COUNTIF({"Major";"Sub"},I4)=0 but this is not working.
View 4 Replies View RelatedFor a project I need to develop excel spreadsheet that will contain two columns. First column will contain list of sale items. Other column will contain hyperlink to the items image as pdf file stored on hard drive somewhere. My requirement is to highlight row containing sale item, whoes pdf file is modified/updated since the last time user has opened the spreadsheet.
View 8 Replies View RelatedI am working for a non profit humanitarian org and I am trying to modify an excel 2003 spreadsheet to change the formatting (color) in one cell based on the value of another cell. Obviously this is easy with conditional formatting, except that I have a list of around 25 unique values and formats.
Could anyone point me in the direction of some code that can do this? I am a relative novice to Excel when it comes to VBA and scripting things.
I've creating a sheet to keep track of quality for my department. What i want excel to do is highlight via conditional format the highest 3 percentages in collum.
Example
85%
90%
91%
99.25%
96%
87%
What i want is to have 99.25%,96, and 91% change to green since they are the highest %
I have 3 rows with numbers across several columns, each cell holding one number only. For example:
R1: 1101 - 1102 - 1103 - 1104 - etc.
R2: 2101 - 2102 - 2103 - 2104 - etc.
R3: 3101 - 3102 - 3103 - 3104 - etc.
A little further down I am having a list where the end user will write down the exact same number. Once the same number has been written into the list, I would like the number shown in the top rows (row 1 to 3) to be highlighted in green, basically telling the user that the number has been entered into the list below and is ok now.
How can I manage this via conditional formatting?
I have been playing around with this for a bit and now have it sorted out. This is a macro that will check a name list and highlight any cell that contains one or more of the names.
View 3 Replies View RelatedColumn B contains dropdown list with 7 items in it. When an item is selected from the dropdown list I would like to highlight cell in the column A of the same row. Different color needs to be highlighted for every items.
View 1 Replies View RelatedI am trying to NEXT my way through a column of comments and highlight the cells containing the key words. Below is what I have put together, but I know it is NOT working correctly....
[Code] .......
I have a column (Column B) that contains some free text and I have another column (column K, rows 3 to 12) that contains a series of keywords.
What I would like to be able to do is if column B contains one of the keywords in column K then that keyword is placed in column C.
There will only ever be one keyword per row.
I am calling a procedure from a commandbar button using on action.
With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With
Private Sub ProcessingRequest(ByVal shtRequest As String)
End Sub
I'm working on an Excel spreadsheet and basicly i'm inserting manually data from a lot of paper forms.
It isn't a dificult thing, but is boring, because I have to do more than 2000 forms per week!
I want to accelerate, and how to do that
Problem: "Address"! The Excel page that i'm working has a column to feel with Addresses manually, but I have a table with all correct Address names from the city that I'm working about in other page.
What I would like is when start typing some keyword from the Address cell, it could show me all the choises of input (based on the correct Address Table)
Excel, by default, show, by exact order, all the choises, already typed on the same collumn.
I don't want that.
- imagine I have the Table 1 (representing the table with all city adresses) with a collumn called 'Address' with:
A1 - Street ZXY
A2 - Avenue QWE
A3 - Street QWE
A4 - Avenue ZXY
when typing on another Table, if I write something like “QWE” show me the A2 and A3 to choose wich I'm going to feel, and the same to “ZXY”, showing the A1 and A4. Other thing, if I write “QWE street” may show A3 also. Keyword is what i'm talking about
Currently running a macro which selects all the rows between two specific words and pastes the selection into another worksheet. However, it runs this search on the entire workbook, whereas I'd just like it to run the search in a particular column (column D in this instance).
This is the code as it currently stands:
VB:
With ActiveSheet
.Range(.Cells.Find("financial assets"), .Cells.Find("liabilities")).EntireRow.Select
End With
.Selection.Copy
Sheets("Paste Currency").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Is there any way to focus the search on only one column?
I want to create a list from a "seed" word that consists of a word followed by a number (ex: seedword100).
What I want to do is to be able to create a list in Excel using that seed word and then have excel produce a bunch of replications of that seed word by incrementing the number part of the seed word by 1 for every replication.
So, for example, if cell A1 = "seedword100", I want Excel to replicate it up to "seedword199" like this...
B1 = seedword101
C1 = seedword102
D1 = seedword103
E1 = seedword104
F1 = seedword105
G1 = seedword106
H1 = seedword107 ...
and so on until the last cell = seedword199 (or whatever number I want it so stop at).
I have data in which there are important keywords within a row (e.g.,# Message: FacesSc0_1) that are 'boundary markers' for data that is relevant to me. What I want to do is to select the data in the column between two such keywords, replace them all with the same specific value, and delete the other rows.
For example a column may look like this:
534.88
534.78
# Message: FacesSc0_1
534.78
534.55
534.59
# Message: FacesSc0_2
534.55
534.55
# Message: FacesSc0_3
534.78
534.55
534.59
# Message: FacesSc0_4
I would like to replace the values in the cells between "# Message: FacesSc0_1" and "# Message: FacesSc0_1" with the value 1, delete the values between "# Message: FacesSc0_2" and "# Message: FacesSc0_3", and replace the values in the cells between "# Message: FacesSc0_3" and "# Message: FacesSc0_4" with value 2. The resulting column would be reduced to this:
# Message: FacesSc0_1
1
1
1
# Message: FacesSc0_2
# Message: FacesSc0_3
2
2
2
# Message: FacesSc0_4
I am trying to set up a worksheet which takes keywords from several cells (user populates these cells) and searches all worksheets for these keywords.
for example, i have C1:G1 set up as possible search string entry cells where the user types their text. I need a function which takes the data in whichever of these cells have been filled in (i.e. it may only be in 1 cell, 2 cells or all 5) and searches each worksheet for all cells containing ALL keywords. (as it finds each one, i will highlight the cell, but that part of the code is obviously very easy )
Currently i have this (note, i havent yet figured out how to but the k1 to k5 variables into the search string yet which is why they are set but not used):
At the moment, its not ever looking past worksheet 1 - can anyone see why? and then how to i get it to look for all entered keywords?
In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc
At first I was trying to find out the most common keywords in column A, and I used the following code to do so
I am busy building a spreadsheet that must do the following.
A
B
C
D
E
[Code].....
Above is a basic example of the data I am using What the spreadsheet must do is it must search cell B1 for the key word in in cell E1, if it does not find the keyword, it must search the same cell for the keyword in cell E2 and so on if it finds the key word it must return a consatination of cell A1 and the Keyword.
This is easy enough to do using the search function together with a couple of nested IF statements but where the problem comes in is that I am needing to run the search on about 12000 lines using over 10000 key words.
currently I have sheet with a formula in Column A containing 40 nested IFs searching for the first 40 key the same formula in Column B searching for the next 40 keywords and so on but to search 10 000 keywords(Client name or Client number) I am needing to apply this formula to 250 columns over 12000 line which makes the sheet very resource incentive.
How do I streamline this to make the spreadsheet more efficient
I've gotten the Filesearch function to work in VBA when using a wildcard after the keywords like this: "SomeFile*.xls".
But I can't get it to work with keywords after the wildcard character like this: "*someFile.xls"
I created yesterday and tested in 5 separate documents and worked fine; I went to use today and it runs (flips through document with no error code) but doesn't change key words to blue/bold anymore. I created in record mode and tweaked because I'm a newbie.
Sub BoldBlueRegionsAcctsMarkets()
'
' BoldBlueRegionsAcctsMarkets Macro
' Makes bold and blue all regions, acct types and market names
'
' Keyboard Shortcut: Ctrl+p
'
Cells.Replace What:="NORTHEAST", Replacement:="NORTHEAST", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Range("A2").Select ..................