Change Format Of Multiple Keywords
May 17, 2008
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 ..................
View 10 Replies
ADVERTISEMENT
May 5, 2014
I need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
[URL]
the VBA code i used was this:
[Code].....
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
Cat_Mouse.xlsm
For example, I modified the code to add the word bat:
[Code] .....
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
View 9 Replies
View Related
May 11, 2009
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?
View 12 Replies
View Related
Jan 22, 2014
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
View 2 Replies
View Related
Jul 23, 2012
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
View 5 Replies
View Related
Jan 15, 2014
I have a big file here : [URL] .....
As you can see, the second column contains abstracts which I want to use to determine categories for each document (each line).
Categories are as followed :
Diabetes prevention -> keywords : sugar, diabet*, insulin*
Obesity prevention -> keywords : weight-loss, fat*, LDL
etc. (5-8 categories)
To target one keyword, I found this formula :
=IF(IFERROR(SEARCH("*diabet*"; $B3); 0); "Diabetes prevention"; " ")
What I would like to know is how to add more than one keywords in this formula.
I would also like to know, if it is even possible, how to put every categories in this column, so that i don't have a category per column ... It's hard to explain.
I want to have in front of each line the corresponding column. It means that the formula has to search for every keywords and select one or more appropriate categories.
View 2 Replies
View Related
Aug 10, 2009
I have created a userform but I am having extensive problems with the date formats.
My system is set to UK and short date is set to: DD/MM/YYYY
When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.
So I finally figured out to use DateValue to format it correctly for example: ...
View 2 Replies
View Related
Jun 12, 2008
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies
View Related
Oct 17, 2012
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies
View Related
Jan 10, 2007
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
View 9 Replies
View Related
Jul 3, 2006
I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)
Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted
View 3 Replies
View Related
Dec 6, 2013
Is it possible to change the contents of a cell (A1) displaying a name as:
John Smith
to Smith, John (in cell B1)
View 3 Replies
View Related
Jul 10, 2014
I 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] .......
View 8 Replies
View Related
Nov 9, 2012
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.
View 2 Replies
View Related
Apr 23, 2009
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
View 9 Replies
View Related
Oct 25, 2006
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.
View 9 Replies
View Related
Mar 1, 2007
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
View 9 Replies
View Related
May 1, 2014
The formula below is to change the fill colour of a date cell prior to the date expiring. The 120 value is number of days
=(E7-today())<=120
The problem i have is it only seems to work if i format the cell and use mmm-yy.
I want to use the same formula for dd-mm-yy..
View 3 Replies
View Related
Dec 23, 2008
See attached picture. In column I need A B C D… replace by 1 2 3 4…
View 2 Replies
View Related
Sep 8, 2009
I need to turn millions (I'm guessing around 4-5 million, though I'm not completely sure since I don't have a program that can actually open the file completely since it's so large) of lines of text which are formatted like this:
05/15/09 09:45:01 AM 16600 1
into lines which are formatted like this:
20090515 094501;16600;1
I could do it by hand, but since there are literally millions of lines like this, it would probably take me about a year to do. So my questions are:
1. Is there a way to do this relatively quickly in Excel?
2. When I open this file in Excel, the year and time tabs aren't filled in with the year and time, but simply with "#######", though if I click on the cell, the year or time is displayed up in the "fx" function bar thing. Is there a way to keep these cells from displaying as "#######"?
3. Right now when I open the file (it's a CSV file), it only opens a little over 1 million of the lines, and an alert pops up that says the entire file was not opened. Is there any way to get the entire file to open? If not, how could I split the file into pieces that are small enough for Excel, and then how could I join those pieces back together?
View 11 Replies
View Related
Apr 26, 2013
I have a column with times in it, it seems to be formatted to HH:MM I would like to change this to HH:MM:SS but it will not seem to change at all by formatting the cells, does this mean I will need to go through each individual time and change it to the new format or is there some sort of work around?
View 6 Replies
View Related
Jan 2, 2014
how to change the cell format (or use a formula) so that the inputs typed below can look like proper dates?
20121231
20111230
20101231
20091231
20081231
20071231
20061229
View 4 Replies
View Related
Jan 17, 2008
PHONE formatting. Is there a way to create a macro to format the VALUE of the phone to be either (XXX) XXX-XXX or XXX-XXX-XXXX ? I obviously can get it to look like this, but the value is just XXXXXXXXXX.
View 13 Replies
View Related
May 27, 2009
when I do Format Cell, the date formats offered use US formats like 20/02/1999. Why does it do this, and can I fix it? I suspect that this the root of the following problem.
I have a button on a userform to enter today's date into a textbox. It enters it as 05/27/2009. The wrong format. I save the data to the worksheet, then use the data form to go to the next record, then back. The date then appears in the correct format as 27/05/2009.
Another related problem: if I enter a date manually into the textbox on the dataform, such as 2/3/2008, it interprets it as Feb. 3, and when I save it to the worksheet, and then use the form to view it, it comes out as 3/2/2008.
View 6 Replies
View Related
Oct 25, 2011
In column M, i have a long series of times (times of day) which are currently formatted as "1654", "1021", "1628", etc. In column AI, i need to reformat those dates to look like: "00:00:00.000"
What would the formula to accomplish that look like?
View 2 Replies
View Related
Aug 14, 2012
I have data in excel sheet named LSR from A2:L3000 sample as below. i want to have a VBA code to change the data as below.
Data before changing
FK
PLATE NO
Vehicle
23
1234DB
toyota
[Code]....
After running VBA code
FK PLATE NO Vehicle FK PLATE NO Vehicle FK PLATE NO Vehicle
23 1234DB toyota 23 98544DB ford
34 34322SW hyundai
21 34344TR nissan
451 43434FR toyota
39 43423TY nissan 39 34445FE ford 39 43455ER nissan 39 343444E landrover
189 44455E nissan
View 1 Replies
View Related
Apr 14, 2013
I have column A1 as 9/8/2012 11:00:00 AM. I would like to change it in the specific format as DD.MM.YYYY HH:MM, so I want it to read as exactly this way 08.09.2012 11:00.
I tried breaking the format as separate date and time n then joining back still no luck as when i delete columns A and B its a total disaster.
I simply want the DD.MM.YYYY HH:MM format
View 5 Replies
View Related
Mar 16, 2007
I'm trying to find a VBA code that will look through all my cells and any cell that equals a certain text, it will change the format to "strikethrough" and put a line through the text.
View 9 Replies
View Related
May 26, 2006
Can Attached code be changed to alter font colour,type. rather than just fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("e:Az")) Is Nothing Then
Select Case Target
Case "Ph"
icolor = 6
Case "Li"
icolor = 12
Case "O"
icolor = 7
Case "An"
icolor = 53
View 3 Replies
View Related
Jan 11, 2014
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?
View 4 Replies
View Related
