Method To Highlight Any Cell That Contains Anything But Allowed Set Of Words
Jun 26, 2013
I have a data set containing text (no numbers) that spans several columns and rows. I have a list of 'allowed' words (about 100 words) that can be in these cells. If a cell contains a sentence with a word that is anything other than these allowed words it should be somehow marked. Ideally the 'wrong' word itself is marked, but it would already be very useful if the cell that contains the word is marked in some way.
I tried conditional formatting, but it seems to only allow formatting cells that contain a certain word, not if it doesn't contain certain words. Is there a function or a script that would be able to do this?
Simplified example:
Say you have four allowed words:
Apple
Pear
Orange
Banana
You have the following cells:
>Apple Banana
>Pear Pear Orange Pear
>Banana Apple Orange Kiwi
The third cell should be marked because it contains a word outside of the set.
View 6 Replies
ADVERTISEMENT
Mar 25, 2007
I have received a workbook containing lists, unfortunately the names are listed in individual cells - several per cell, separated by commas. I need to highlight wherever a particular name occurs without splitting the data from the cell, ie like Find but within the cell.
View 9 Replies
View Related
Nov 5, 2012
i need to word to be high light or underline for misspelled words in excel need vb or macro.
View 9 Replies
View Related
Nov 6, 2008
Sample data (all in column a):
Heading 1
123
12345
1234
345
Heading 2
987
9876
Heading 3
785
Heading 1
2356
8525
Heading 2.........
View 9 Replies
View Related
Jun 6, 2013
I have Excel 2007. I have a spreadsheet with email addresses in it surrounded by other text. I want to automatically highlight in yellow each row containing the words gmail, yahoo, hotmail and so on.
I CTRL-H then enter the info as you see it in the pic attached. When I click Replace All it says "that function is not valid". It works like a charm for every other word I test it with.
View 3 Replies
View Related
Jan 23, 2007
Using VBA are more than 7 arguments allowed with if-then?
View 7 Replies
View Related
Jan 19, 2009
I work with large charts with many users updating them. With people not following directions metrics are getting messed up. I'm trying to come up with a macro that when someone tries to paste from a different version, or something they copied earlier, the workbook/worksheet will not allow it, so that they have to type in the required information to avoid errors. I'm now working in Excel 2007. It would be nice just to have this option within the program without having to lock down the workbook.
View 4 Replies
View Related
Jul 23, 2009
i want should be done as a function or VBa. I would like to have A Column so that only postive numbers can be entered. Disallowing 0 and below from being entered into cells.
View 2 Replies
View Related
Oct 4, 2012
how to setup formula for not allowed duplication in the column
View 1 Replies
View Related
May 29, 2013
I'm trying to write a code to import the excel file into access. Here is the code..
Private Sub Command8_Click()
Dim strPathFile As Variant
Dim strFilter As String
Dim ahtAddFilterItem() As String
Dim ahtCommonFileOpenSave() As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
[Code] ........
View 2 Replies
View Related
Aug 22, 2013
I have a code that I need to limit the entries allowed on an input box. I have got it limited to a single value ("S1") but I need to add a second ("S2"). If "S1" or "S2" is not enter I need the input box to reappear until the right value is entered.
My code:
Code:
Repeater:
dbl = InputBox("Enter the S1 or S2 for the rack location you are using", "Rack", "S1", 5, 275)
If dbl "S1" Then GoTo Repeater
End If
View 2 Replies
View Related
Feb 20, 2007
I have our company telephone accounts and im trying to highlight any numbers that are not on our approved dialling list. What I would like to happen (ideally) would be for a formula to look at the cell next to it and compare it with the approved numbers list, if it doesn’t appear on the list then it would do something to let me know, like put a big red X in the cell (if it could make me a cup of Tea instead that would be better).
View 9 Replies
View Related
Aug 15, 2013
Im building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.
I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)
The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)
My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.
View 2 Replies
View Related
Jul 28, 2009
I keep getting an error in this code while trying to write the results of a query to a sheet in the current workbook. The error i get is 'operation is not allowed when the object is closed'... since i didnt close the recordset before trying to write the results to the worksheet, im at a loss to explain why im erroring...
Public Sub fillWorksheetWithData(dataSheet As Worksheet, conStr As String, sqlStr As String)
'clear datasheet
LastRow = dataSheet.Range("A65536").End(xlUp).Row
If (LastRow < 2) Then
LastRow = 2
End If
dataSheet.Range("A2:A" & LastRow).EntireRow.ClearContents
View 9 Replies
View Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 Replies
View Related
Feb 13, 2012
I am having trouble making any formula references between 2 different workbooks.
For example, I being typing out my VLOOKUP function on 1 sheet, as soon as I get the table argument, I switch over to the other workbook in which the table is, and I no longer have the VLOOPKUP function active.
I am having this problem for any formula in which I am trying to reference this workbook.
I have ensured they are both saved as Excel workbook documents. I am using Excel 2007
View 3 Replies
View Related
Sep 20, 2006
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies
View Related
Jun 3, 2014
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
View 7 Replies
View Related
Jul 16, 2009
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
View 9 Replies
View Related
Jun 23, 2006
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View 3 Replies
View Related
Feb 4, 2013
On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies
View Related
Jan 22, 2010
I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies
View Related
Nov 9, 2006
I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies
View Related
Feb 15, 2007
I have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.
Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
View 2 Replies
View Related
Jun 16, 2009
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 71 bytes) in /home/eforum/public_html/search.php on line 1155. I have following macro, what worked just fine in 2003. Now I'm working with 2007 and I can't set up the Microsoft Visual Basic for Applications Extensibility library. Here's the
View 2 Replies
View Related
Oct 28, 2008
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies
View Related
May 8, 2014
Looking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
Name Spreadsheet Ex.xlsx
View 2 Replies
View Related
May 26, 2009
This formula counts the number of spaces between words in a cell:
View 5 Replies
View Related
Jul 16, 2012
I have created a userform and a command button to bring up the user form but when I click on the command button and the user form pops up I am not able to enter any data, the entire page freezes
This is the code
Private Sub CommandButtoncancel_Click()
unloadme
End Sub
Private Sub CommandButtonOK_Click()
With Workbooks("RETS results version 2.xlsm")
[Code] ......
View 1 Replies
View Related
Jul 23, 2012
I need VB codes for an activity in excel. Im trying to create an example file for the suppliers to enter keywords (Search keyword) where it should generate different formats of the search (WORDS) and its case sensitive. Suppliers would be typing the keywords in A2 and it should generate the Permutations horizontally against the keyword (B2 C2 D2 E2 etc). Let me know if you need more clarifications.
Example given below and also attached the file for your reference.
New Keyword – Singular Proper Case
Singular-PR
Singular-LC
Singular-UC
[Code]....
View 4 Replies
View Related