VBA Macro To Delete Entire Column When Background Cell Color Is Red
May 17, 2013
with performing the following tasks with VBA:
1). For each cell that in the range that has a Red background delete the entire column
I have attached a sample spreadsheet where I have tried to do this. It's not quite working. It does not seem to work when there are RED cells next to each other.
I know that I can do a simple thing like column("A:A"), but I'd rather have it in a loop as in the sample code, in case the columns change in the future.
2). The second task is to delete the rows in the spreadsheet where the first cell in the row is a blank.
View 4 Replies
ADVERTISEMENT
May 7, 2014
I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row
View 4 Replies
View Related
Mar 9, 2007
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:................
View 5 Replies
View Related
Sep 28, 2013
I have a data base in which I classify cells in three colors (green, yellow, and red) on my own. I would like the first cell in each column to indicate if there is a red or yellow cell in it's column. Because red cells are more important then yellow cells, if there is a yellow and a red cell in the column, I would like the red to over ride the yellow and the first cell would indicate red.
So basically, if there is a red cell in the column, no matter what other colors there are, the first cell should show red. If there is only yellow or yellow and green cells, the first cell should show yellow. If there are only green cells or green cells and clear, the first cell should show clear.
View 1 Replies
View Related
Sep 17, 2008
My boss wants a spreadsheet that has multiple buttons on it that will change the background color of a specific cell to four different colors. Example:
Text written in Cell B3, Button in Cell A3 that will change the Background color of Cell B3 to either Green, Yellow, Red or Blue.
There will be a lot of buttons on this worksheet following the same format as above. I don't want to change the value of what is in the cell, just the background color.
View 7 Replies
View Related
Mar 29, 2013
I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.
View 8 Replies
View Related
Feb 20, 2009
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
View 2 Replies
View Related
Mar 27, 2013
I'm trying to have a macro run to search a specific column (S) for a particular text value "y" and if true it will change the font to bold and the color to red for the entire row of the cell that contains the "y". the column is part of a data table that is constantly refreshed not sure if that makes a difference or not.
View 2 Replies
View Related
Mar 26, 2007
Delete entire Row if cell in column contains "Dog" in it.?
Example,
duck321
dog123
cat123
dog123
duck321
cat123
so after it would look like this
duck321
cat123
duck321
cat123
View 9 Replies
View Related
Oct 16, 2013
If cells in column A3:A10000 are blank / unpopulated I would like to delete the blank rows.
View 6 Replies
View Related
May 9, 2008
I'm using the following code to delete rows that I don't want to include and I've ran into some more things that need to be deleted...
For lLoop = RngCol.Rows.Count To 2 Step -1
Select Case RngCol(lLoop, 1)
Case " Date:", "Skill:", "Agent Name", "~*", "*Train*"
RngCol(lLoop, 1).EntireRow.Delete
End Select
Next lLoop
An example of "~*" would be: ***SICARII***
An example of "*Train*" would be: Ozgrid Train1
It's not recognizing these new cases. Do I have to utilize FIND? (since CTRL+F does work with the given cases)
View 4 Replies
View Related
Dec 22, 2009
I have a column chart that goes 0-100%. The back wall is red from 0-60%, yellow from 60-80%, and green from 80-100%.
To do this, I shaded some blank cells to the same proportional shape, did a screen capture, saved that image as a file, then imported it back in as the background.
Problem is, those color breakpoints (60%,80%) may change, and that process is a pain. Is there an easier way to do this such that if I change the values the background will change automatically based on the shading of some cells?
View 9 Replies
View Related
Aug 6, 2008
I am trying to develop an Excel spread- sheet by using some macros. In detail, in my first worksheet, I have something like -
Exp1 ....
Exp2 ....
Exp3 ....
:
:
:
Expn ....
This rows are coming from another worksheet and I have used a macro (developed by me) to populate these rows. Even the number of rows to be populated is not fixed.
However, I would like to add one row with text - " Total Expense" after those rows. This row will have some background color - say Tan - and font color - say Green - and also a border around its own - in nature, the border will be thick, Double and with color Rose. I have tried the following code in macro but not working properly -
Sub Include_Fields_n_Format(loc As String)
tot_exp_scell = loc & f_scell
tot_exp_ecell = loc & (f_scell + 1)
Range(tot_exp_scell, tot_exp_ecell). Merge
Range(tot_exp_scell, tot_exp_ecell).WrapText = True
Range(tot_exp_scell, tot_exp_ecell).Interior.Color = RGB(150, 150, 150)
With Range(tot_exp_scell, tot_exp_ecell).Font...................
View 4 Replies
View Related
Oct 21, 2008
I have an excel sheet in which many cells have a background color of Green. These cells are located all over the sheet. I want to change the background color from Green to Red not affecting the color of other cells in the sheet.
I will be realy really thankful, as I have to do this exercise for more than 50 worksheets.......If I start doing it manually by selecting each celll with green color background and changing it to red.....you can well imagine my condition.....my life would get shorten significantly while doing this.
View 6 Replies
View Related
Apr 20, 2014
i am looking macro code to extract data from sheet1 to sheet2 & sheet3, with criteria background color :
1) extract data from sheet1 to sheet2 for data contains/highlighted yellow color background
2) extract data from sheet1 to sheet3 for data contains/highlighted red color background
For detail information, see attached workbook : extract after coloring.xlsx
View 3 Replies
View Related
Mar 15, 2013
creating a macro. I would like the background color of certain cells under column A (cells 12:25, 27:32, 34:38, 40:45, 47:51, 53:61, 63:70, 72:79, 81:88, 90:92) to change to the color green and display the text "Complete" on mouseclick.
Before the cell is clicked, it would be the color red, with no text. (I'm not sure if you need to know that).
Next, I would need a macro that resets everything back to red. I hope this macro could be assigned to a form button.
View 1 Replies
View Related
Apr 19, 2014
I am looking some macro code to extract data based on background color (yellow color) from sheet1 to sheet2
for detail like this below :
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
[Code] ........
View 5 Replies
View Related
Oct 6, 2006
On one worksheet I have a list of colours in cells. I want to be able to look in the list the list and return the background colour of that cell. The function i am using is:
=CELLCOLORINDEX('1.1'!$C28,FALSE)
'1.1' is the worksheet name, $C28 is the cell (coloured blue)
It seems to work because I am getting the correct background colour in the new worksheet, however it also returns the number(37) as well.
I just want the background colour to be shown only, not the color's number as well.
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If
End Function
View 9 Replies
View Related
Apr 2, 2012
I'm working on a sheet that gets filled in with background colors (red, light blue, and green) and I'm trying to come up with a way to automate the process of inputting the text in the red and blue fields (red always gets PROCESS and light blue always gets SHPD) but they end up in different locations in the same columns. is there a function or macro to make this happen? I have constructed a macro to compile the sheet from my online database as it is a live system.
MODELACTIONxxx1xxx2xxx3xxx4xxx5xxx6xxx7xxx8xxx9xxx10xxx11xxx12xxx13xxx14xxx15xxx16xxx17 This is a simplified version of what I'm working with, and i'm on Excel 2000
View 2 Replies
View Related
May 15, 2014
I have an excel spreadsheet that 7 different categories (eg. Ave waittime, Avg loadtime, etc) that I have conditionally formatted based on 5 ratings (each rating a different color Green, light green, yellow, red, and purple) I would like to be able to give each cell a value based on what color it is eg. Green=5, light green=4 and so on.
View 2 Replies
View Related
Mar 28, 2014
i am looking macro formula to delete row with 2 criteria :
- delete rows if yellow color background and based on adjacent cell (blank cell), otherwise keep rows when adjacent cell not blank
View 8 Replies
View Related
Nov 17, 2009
I want to change the color of a cell's background based on valid data in the cell. I have a pull-down list, it uses a named list, and this all works fine. I want the cell to be red, and then after the user selects a valid entry from the pull-down list, I want to change the cell to yellow.
View 2 Replies
View Related
Jul 10, 2012
I have 10 excel sheets that are filled with data some 20,000 each.
Some of the descriptions in that row need to be changed.
I wonder how I can create some kind of macro or formatting that will change the text color or the fill color as I make those changes automatically?
I can sort the list after that easy enough, pulling the color text or the fill color.
View 12 Replies
View Related
Feb 4, 2014
I need a macro that would change the cell background color in a calendar according to list of dates found in a table (Holidays & Vacations) which can changes on a yearly basis.
View 6 Replies
View Related
Nov 11, 2008
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.
View 4 Replies
View Related
Jul 4, 2014
I am currently using this Formula to Search a table on sheet 2 (Tabelle2) and copy the date in the relevent cell (Cells) into the correct cell on Sheet 1 (Master Sheet).
[Code] ..........
Used in cells E10:AZ30 and E33:AZ46
This works perfectly and is the formula behind all relevent cells in Sheet 1.
I would like to keep this but to add that it also copies the background color of the cell in Sheet 2 to the cell in sheet 1.
If i can Bring this Formula into VBA and add the color changing part, It must only work on the cells listed above..
View 9 Replies
View Related
Jun 13, 2013
I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
View 4 Replies
View Related
Jul 1, 2014
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies
View Related
Aug 7, 2014
I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
View 6 Replies
View Related
Dec 4, 2013
I have about 30 spreadsheets that I need to sort data. I'm using a Excel: Mac 2011.
Each sheet has about 100 rows and 80 columns.
I need to extract the rows that have a colored cell somewhere in the row. There are three types of colored cells: black, green, and red. The green and red have X typed in them too. The black cells have D typed in them.
Every row in the sheet has at least one cell with an X in it (colored or not).
I just need the rows with a colored cell to be extracted or filtered from all the rows without colored cells.
View 1 Replies
View Related
