I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.
Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.
The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!
My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.
I have a pivot table full of data that needs a macro/vba/something to loop through it and hide all rows that have no fill color. The table had 210 rows so I want a quick way of hiding the irrelevant ones (only the ones filled in green or red), and so all the ones with no fill need to be hidden. Something like a button to hide/unhode would be ideal.
I want to write a macro to fill color in row 8 of the worksheet as solid black . Also columns P,T,AP,BC ,BE and BG should be filled solid black and width should be 1 for the row and the column.
Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -
I have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
If I use Conditional Formatting I can only use 3 colors, I'd like to use 8 or 9. Is there another way to do this without using the Conditional Formatting?
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.
Column A Column B Expected Action
Press F3 N Its Fine
Verify this.. Y Its Fine
this.... Y Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"
Verify This. N Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".
I'm trying to discover the best way to accomplish formatting a cell by filling it a certain color based on 2 of 3 criteria of other cells.
Column A has the title of the field. Cells B1:D1 have one of 3 values: Nothing/blank, Complete, or x. If all of the x's are gone within the range of B1:D1, I'd like to automatically format cell A1 to fill in Blue (or any color).
1. By entering a numerical value in Cell A1 for example typing 5. 2. Then, next five columns would be blocked with specific color. 3. if i am typing 10, then 10 columns would be blocked with specific color.
I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.
In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.
I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.
In Column L it has either numbers 1 through 100, or the letter "D".
What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.
Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".
It might be easier to do it separately because once you run the part that deletes the rows that dont have a C or LP then I could probably just do one that says if column L = D then change to 1.
Lets see if I can simplify this for you.
If Column L = D and Column A = BLANK then Delete Row (this would delete all Ds that dont have the C or LP)
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
I've been looking through various forums looking for a macro for a particular task, but I have not found something that works yet. Maybe someone could help me out? It's pretty straightforward situation.
I need a macro that removes an entire row when: information in column B and column C are both found in other rows ( duplicates).
I'm dealing with a worksheet with store record information ... Store Name (column B), Street Name (column C), State/Province, etc. I import new store records all the time and many are invalid because they share the same name and street address and therefore need to be removed. The ones that Ive played with unfortunately end up deleting the cells only and not the entire row.
It's a tedious process to do manually remove entire rows (for duplicate column B&C) when dealing with thousands of rows and I would very much appreciate any assistance or insight into how simplify the process with a macro. I've attached a sample of what I'm working with.
I am using conditional formatting to apply a light green color (index number 35). Is there a macro that can delete the cell contents of the cells with this formatting in col K and L?
I used this macro to find the duplicates in column B:
Sub KryDups() ScreenUpdating = False FirstItem = ActiveCell.Value SecondItem = ActiveCell. Offset(1, 0).Value Offsetcount = 1 Do While ActiveCell <> "" If FirstItem = SecondItem Then ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0) Offsetcount = Offsetcount + 1 SecondItem = ActiveCell.Offset(Offsetcount, 0).Value Else ActiveCell.Offset(Offsetcount, 0).Select FirstItem = ActiveCell.Value SecondItem = ActiveCell.Offset(1, 0).Value Offsetcount = 1 End If Loop ScreenUpdating = True End Sub
The duplucate cells are now red in color. (RGB(255, 0, 0)). How do I now code VB to delete the rows in column B where the cell color is red? Here is some of the code that I tried:................
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB: Public Sub DelRows2() Dim Cel As Range, searchStr, FirstCell As String Dim searchRange As Range, DeleteRange As Range
In my excel sheet, data present in the column are in dynamic range. After that I want to delete or clean all blank column which is bordered and in fill color. What VB program I have to write.
E.g. if data is present upto column 'J', then I have to clean all columns from 'K' onwards..(where bordered and fill color there)
