Macro To Color Alternating Rows
I was wondering, what would the macro be to colour alternating rows.
So I could select the range of rows, hit the macro - and it would colour them for me?
I need it to do alternating green / lightgreen. For one, but I need to do other colours afterwards as well. So what would the code be to do this?
View Complete Thread with Replies
Related Forum Messages:
Copy Paste Alternating Rows
In column H I have dates from 1/1/2008 to 12/31/2013. What I would like to do is transfer that data to column J, but alternate it every other row and then insert the word Date into the blank cells. For instance J1 would read "Date" and then J2 would read 1/1/2008 J3 would be Date and J4 would be 1/2/2008. I have a large database and I tried to use array formulas and they slow it down to a crawl so I was working with DSUM, but I don't feel like wasting time with adding in Date manually.
Shading Alternating Rows In A Range
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
Macro: Changing Color Of Coloumn Header Based On Rows Color
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
Quickly Select Cells In Alternating Rows
I am interested in selecting cells in alternating rows as follows:
12 Red Happy
00 Unique descriptor
13 Grn Sad
03 Unique descriptor
14 Yel Happy
02 Unique descriptor
I am interested in selecting all the "12, 13, etc", "Red, Grn, ..." and "Happy, Sad..." for analysis, but want to skip all the rows containing the 0- numbers and the descriptors for them. How do I quickly select only the alternating cells in a column within a selection?
Conditional Formatting Similar To Shading Alternating Rows
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
Color Rows With A Macro
I need a macro to color rows as follows -
If the cell contains: Closed then highlight the row Light Red
If the cell contains: In Progress then highlight the row Light Green
If the cell contains: Pending then highlight the row Yellow
If the cell contains: Assigned then highlight the row Light Blue
Macro To Color Cells Based On Their Font Color
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
Alternating Range Formatting
I'm writing a macro thatís taking user input (from a form I developed) and will generate a chart according to the input.
One requirement for this chart is that the background alternates fill colors between gray and white. Every four columns need to be either gray or white. Based on the duration the user entered, the width of this chart is created. The length (the number of rows) is constant.
I've tried different loops (for, if, do - you name it I tried) This loop is just out of my league. Any and all help would be greatly appreciated.
Summary of Facts:
- Starting Point of Chart is B2 (the top left corner of the chart will be in B2)
- the number of columns is dynamic (based on user input)
- The rows are constant are (2 through 16)
- Every four columns need the fill color to be either gray or white
Select Alternating Columns With Unknown End Point
so I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.
I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.
I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.
Anyone know what I am talking about here?
I know it should be fairly simple but I couldn't seem to find any answers anywhere else.
Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.
Separate A Line Of Alternating Text/Numbers Into Columns
I have thousands of lines of data that are of the following form:
Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2
Jacksonville, FL 3577 Aetna 47 BCBS FL 37
Knoxville, TN 3796 Cariten 44 John Deere 41
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29
I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)
The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.
Is there any formula I can use that would be capable of solving this?
Color Only Even Rows
how you make the following happen in excel using VBA: I want to make all the even rows a different colour (light grey) all the way down the sheet, so row 2, 4 , 6 etc. I want to leave the odd rows normal.
Ideally I would like the vba to work out the last row and stop there. My sheet is called 'Final Rep'
Find Rows By Color
I'd like a macro to do this
Put the word "Yes" in the "Red" column if any cell in that row is cell (except the header row).
This was not done via conditional formatting. These red cells are the result of many different subs, so modifying these existing subs would not work as I wouldn't know where to begin modifying each one........
Color Blank Rows
I have a worksheet that contains column A7 through M7. Row 7 contains the header. Currently I have a small vba procedure that inserts a blank row whenever the data in column G7 changes. Here is the code that does this.
Changing Color Of Rows
I have a spreadsheet where you can choose one of two options per row.
The listbox says either CASH or CHECK. If the user selects CASH, I would like the row to stay the same color. If the user selects CHECK I would like the row to be highlighted or change color. Upon a glance, anyone who knew the system could then tell how many people paid by CHECK, and who they were.
Remove/Delete Rows By Color
I have a spreadsheet with multiple colored rows (grey rows are company headers, white rows are orders). I would like to find and delete any multiple company headers (i.e. grey rows followed by another grey row) and keep only the company headers that have orders (i.e. grey rows follwed by white rows). I have attempted the following macro but it doesn't seem to be validating the initial "If" since I keep receiving my "no good" ...
Color Rows Based On Row Citeria
I have a range of data 9cols x 600rows which I want to apply 9 conditional formatting styles to. The range has other data in subsequent columns that I don't want the formatting applied to.
The condition is quite straight-forward, but I'm damned if I can find out how to do it. Only a single cell on any given row will contain data (it's a error if there's more than one value on the row). Based on the column the data is located in, I'd like to assign a background colour to the segment of the row being analysed.
Diagramatically (where * = blank)
* * * * * * X * * (Colour this row segment some colour)
* X * * * * * * * (Colour this row segment another colour)
* * * X * * * * * (Colour this row segment another colour)
* * * * * * * * * (Colour this row segment another colour)
X * * * * * * * * (Colour this row segment another colour)
X * * * * * * * X (Colour this row segment red - error)
This is as far as I've got and it's not working.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim cell As Range
Cut And Paste Rows Based On Color
I have an excel sheet with over 10,000 rows. Some of the rows are colored. (Light Green). I want a macro which starts from 1st colored row, cut or copy all the rows (including the colored row) before next colored row comes and paste it in the same workbook but different tab. It would also be good if macro creates the tab itself.
Auto Color Shading Of Rows
Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
Hide Rows Based On Color
I have the following code to hide rows based upon whether or not the cell in column A is black or not.
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A36635")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 1 Then
MyCell.EntireRow.Hidden = True
I was thinking of taking this one step further and having the rows hide automatically once the cell is turned black allowing the user to bypass having to click on a macro button.
Automatically Color Rows Based On Cell Value
I currently use spreadsheets to report on backup processes.
I am trying to speed up this process and one thing that woudl help me is a function that will automatically fill the row with a colour depending on the choice in the dropdown box, so if you choose 'successful' from the dropdown list the row colour would fill to green and if you select 'failed', the row would change to red and a choice of 'completed/errors' would result in orange.
This process is currently done manually and often results in errors like a succesful result with a red filling.
Is there anyway to automate this process? i was trying to whip up a macro, but have been unsuccessful so far.
Delete Rows Based On Fill Color
i found a link to my problem in the subject title below,
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.
Filter Rows Based On Background Color
how to filter rows based on the background color used in a cell? I need to make a macro that will filter/hide all rows where the cell in column B has the background color set to Yellow. Is there a way to test for that so I could get a true/false value in a column to filter by?
Color Rows Where To/From Row Numbers Reside In 2 TextBoxes
I am currently trying to make some VB code that will allow me to offset and select cells depending on a couple of variables.
I have a UserForm that requires two numbers from the user. These numbers will correspond with the cells I want to highlight (the strings from the TextBoxes in the UserForm are called 'test1' and 'test2' in the code shown below)
Dim test1 As String
Dim test2 As String
Private Sub CommandButton1_Click()
test1 = Roww1.Text
test2 = Roww2.Text
Worksheets("Sheet2").Range("A1").Value = test1
Worksheets("Sheet2").Range("B1").Value = test2
When this script is run, I want to end up with some selected cells in sheet1 that match up with the numbers the user enters into the UserForm.
Color Rows For All Cells In 1 Column Meeting Condition
A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Selection.Font.ColorIndex = 3
Color Table Rows Where Specific Cell In Row Matches Condition
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.
Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Selection.FormatConditions(1) . Interior.ColorIndex = 16
Automatically Color Fill Rows Based On Changing Column Information
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.
Copy & Append To Report, Rows From Multiple Sheets If Any Cell Meets Format Color
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.
On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.
Macro - Color Coding
I am trying to figure out how to run a macro for 1 particular workbook in an excel spreadsheet. I don't want it run on any of the other workbooks in that file, just the 1. I can't use conditional formatting because I need more than 3 values (if statements).
This is what one example of data in a cell and below is the color I'd like it to turn when I run the macro:
Anything that has a A: (which means an actual date it happened) I'd like the cell to turn blue. If there is an RT: (which means revised target) I'd like the cell to turn red which means it missed it's target date, and has been revised. If it has a TBD I'd like the cell to turn pink (or yellow or any color really). All other cells are just white. I don't know where to find the color codes in excel as well. Other samples of what cells look like are below.
Here is another example:
And final example:
Macro To Change Tab Color
I keep recording this macro, but the problem I run into is that the active sheet is always the specific name of the sheet. I need a general name so that the macro will work on any given sheet. On the sheet I am viewing, I simply want to change the tab color to black using a macro.
Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
Color Code Mistakes From Macro
I am trying to color code mistakes I find when running formulas in a macro. Right now I have it set up to color code mistakes red using the following code after the formula has been inserted in column E.
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Selection.FormatConditions(1).Interior.ColorIndex = 3
My issue is, some of my checks have more than one variable as a wrong answer. For example, one check may include Check and Bad as possible outcomes of the formula in the cell.
the code required to allow two different variables to cause the cells to change color.
I tried a second set of the code for the same column and changed the formulas word to Bad but it did not highlight the cells that came out with Bad as the outcome of the formula.
Allow User To Color Selection From VBA Macro
I see where I can use the following to bring up the color pallet
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
' brings up the colors dialog
But I don't know where to go from here. I would simply like to have cells selected, run the macro, select a color from the dialog, press the ok button, and have the selected cells colored per the color selected in the dialog. (Of course, pressing cancel would exit without changing any colors.)
Macro To Change Font Color
macro that will change the cell color based on text? I have a list of titles in column B. Everytime the word 'Car' is found in this column, I would like to change the font color in columns C-G to the color white within the same row.
Color Macro Command Buttons
I have a workbook which employs 4 different userforms. Navigating thru the workbook, the user ends up at a sheet with 5 macro buttons, allowing them to choose from several different functions. I added these to the worksheet using the "forms" toolbar.
My question is this: Is there anyway to change the color of these buttons, as I can with the userform buttons? Under "format control" I can change size, font, even color of font, but I can't change the background color of the button itself. Is it possible to identify those particular buttons thru VBA and adjust it's format?
Background Color And Border Around Macro
I am trying to develop an Excel spread- sheet by using some macros. In detail, in my first worksheet, I have something like -
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...................
Macro For Changing Background Color Of The Cells
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.
Macro To Format Half Of The Cell With A Different Color
I have a list of records that will eventually be used to create a pivot table. Sometimes an account number will be listed twice but instead of adding to the previous info, it needs to start a new record. I decided in these instances to mark the account number with a character (+). Even though the + will designate a separate record, I'd like it to be invisible.
Is there a way to automatically turn the font color white for only the +? Since I've automated the rest of the process using a macro, a VBA solution is fine.
Macro To Color Cells Based On Criteria
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1%
I need to color code Col F based on the following criteria:
If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?