Conditional Text Deletion From Cells
May 13, 2008
I'm getting a type mismatch (Runtime Error 13) with this code. What I want it to do is delete the contents of the cell if it contains 'N/A'.
Sub RemoveNAs()
Dim word
word = "N/A"
For Across = 1 To 5
For Down = 2 To 150
Cells(Down, Across).Select
If Cells(Down, Across) = word Then
Cells(Down, Across) = ""
End If
Next Down
Next Across
End Sub
View 4 Replies
ADVERTISEMENT
Dec 27, 2006
I have read several posts on this subject as well as the two common methods recommended by Dave. Hovever, I have not been able to incorporate the other thread recommendations to my specific problem. I want to delete entire rows based on the absence of a matching criteria from a list on another tab. Example: Tab1 contains a list of names in A1:A15. Tab2 contains rows of data wherein column C lists names that may or may not be in the list on Tab1. I want to delete rows that have a name in column C that does not match any of the names in the list on Tab1 A1:A15. Here is the code I have pieced together thus far:
Sub Delete_elimntd_players()
Application. ScreenUpdating = False
Sheets("QB").Select
Range("C2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> 'NEED CODE HERE' Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Jun 9, 2007
I have been pulling my hair out for hours now trying to figure this out. I want is to figure out is how to compare an address in Column C to an Address in Column H, If they don't match I want excel to delete the row. I have 7500 rows to compare. I have read lots of posts and looked at the code for delete row conditional but I don't seem to be able to put it all together.
View 2 Replies
View Related
Apr 3, 2009
Here are the examples (pictures):
Example 1 Before
Example 2 Before
You can see that it's an history of currency trades (Forex). What I want is to see how much money one would have made letting only one open position per currency pair. A basic algorithm would be something like this:
View 5 Replies
View Related
Aug 24, 2009
I have a SS in 2007 with 7 text boxes on it. When I go into developer and select them, they all have the same name in the name box of TextBox 68.
I would like to be able to clear all the text in the boxes and have yet to come up with any code to tackle this. Why do they all have the same name?
View 8 Replies
View Related
Apr 11, 2007
How can I protect individual cells in a range from being deleted when I want to delete data from the surrounding cells??
View 3 Replies
View Related
May 22, 2007
I would like to protect some cells of a spreadsheet, without using Excel's protection, because I only want to prevent the deletion of those cells - the cells must remain editable.
View 4 Replies
View Related
Jul 18, 2006
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies
View Related
May 3, 2013
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
View 5 Replies
View Related
Sep 12, 2007
i have made a document for other users to use. how can i limit the paste function to ONLY ALLOW PASTE SPECIAL-- i wish to aviod damage to formatting and formula etc
View 3 Replies
View Related
Oct 6, 2008
is it possible to delete/remove all non use cells
for example if i only want the cells A1 THROUGH TO O31
all the rest removed??
View 5 Replies
View Related
Jun 13, 2013
I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.
There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero
A1 START TIME
B1 END TIME
C1 HOURS WORKED ARE CALCULATED using a formula
D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0
I tried various ways with isblank but just get true or false.
View 2 Replies
View Related
Aug 4, 2014
I have a range that contains dates, with some cells within containing text (e.g. "TBC" or "planning will occur wk42"). I have basic rules as below:
- highlight red if older than today
- highlight green if today or in the future
The problem I have is that the cells that have text are highlighted green which I don't want. I also don't want to have to go through the range removing conditional formatting from each of those cells each time I copy over new data. Is there a way of telling excel to not use conditional formatting on those cells that contain text so I can manually highlight as I need? Some have text as that is how I receive the data.
View 2 Replies
View Related
Dec 30, 2012
I want to conditional format based on all of the following cells contain the same text as AT19 or not.
A19 H19 O19 V19 AC19 AJ19
What is my formula?
View 1 Replies
View Related
Mar 24, 2014
I'm trying to create a conditional format that formats only cells that do not contain one of the names from a range of cells.
For example:
Cell O2 contains Florida
Cell O3 contains Texas
Cell range W1:W2
W1 = Florida
W2 = Arizona
In this example I want Texas to have a strikethrough, but Florida to be unchanged since Texas is not included in the list.
I keep getting an error when I try to do a format of text that does not contain =W1:W2.
View 2 Replies
View Related
Jan 8, 2014
I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".
View 2 Replies
View Related
Mar 2, 2014
I am working on the monthly duty roster that is attached. I have the cells at the bottom configured to total how many early shifts, late shifts and off days each Trooper has. My problem is that I often need to add text to show what type of leave is being taken, or numbers to correspond to the special notes section numbered 1-24, and so on.
Is there any way to use conditional formatting to do this so that the totals at the bottom still sum correctly? Badge numbers are used to identify each Trooper. I have included them at the top for reference. The reason that I referenced them to total by cell instead of individual badge numbers is that I am attempting to format this so that any badge numbers can be entered at the top. This form will be used by other groups of Troopers if I can get it to work.
View 6 Replies
View Related
Aug 28, 2009
I have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".
Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.
Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.
View 3 Replies
View Related
Feb 17, 2012
I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"
The blank fields get the light green fill, but no text; that is, the value displayed is still blank.
I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).
This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.
I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].
I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???
I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.
I also made sure the option [x] Show a zero in cells that have a zero value is checked.
With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.
And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.
View 2 Replies
View Related
Sep 16, 2012
I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:
If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))
This is my macro:
Sub SetVoters()
'
' SetVoters Macro
'
'
Sub SetVoters()
'
' SetVoters Macro
'
'
ActiveSheet.Unprotect
[Code] ......
View 1 Replies
View Related
Jan 25, 2013
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
View 6 Replies
View Related
Sep 8, 2013
How to colour cells depending on text in other cells, for example,
I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?
I have included an example below,
Do you use a "*FSM*" around the formula?
[URL] ...........
View 7 Replies
View Related
Jan 3, 2013
I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank
2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet)
3) A string that exactly matches an item in the lookup table
4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses
=VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value.
If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
View 4 Replies
View Related
Oct 22, 2013
what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?
View 6 Replies
View Related
Jan 14, 2009
I need to create a formula that will give me True or False if text in a cell matches any cells in a range.
For example:
Column A and B have text:
Account
Date
Name
Species
Column C has other and sometimes matching text:
Name
Deal
What formula would I use to find out if all cells in Columns A and B are represented in Column C?
View 3 Replies
View Related
Jul 27, 2009
I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
View 4 Replies
View Related
May 31, 2013
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
View 3 Replies
View Related
Dec 20, 2008
As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.
Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?
Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 30, 2014
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related