Macro To Color Alternating Rows

Apr 13, 2009

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?

ADVERTISEMENT

Color Alternating Rows

Nov 29, 2006

to tint/color alternating rows with white on my worksheet so when new rows are entered the sheet will adjust maintaining this pattern

View 6 Replies View Related

Alternating Color For Groups Of Rows

Aug 1, 2014

My spreadsheet has rows with inspection numbers in column A, but there are varying numbers of rows associated with an inspection number. I'd like to change the color of the rows for every other inspection number to yellow using a formula. I think it has to involve an if/then statement but I can't figure out how to word the formula to just choose every other group of inspection numbers.

Example:
inspection numbers
date
description

[Code].....

View 5 Replies View Related

Macro: Changing Color Of Coloumn Header Based On Rows Color

Jan 15, 2009

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.

View 8 Replies View Related

Shading Alternating Rows In A Range

Nov 30, 2006

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.

View 7 Replies View Related

Copy Paste Alternating Rows

Jan 28, 2008

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.

View 8 Replies View Related

Count Groups Of Alternating Rows Across 3 Columns?

Jan 18, 2014

I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.

1Column 1Column 2Column 3
2FALSEFALSETRUE
3FALSEFALSETRUE
4FALSE TRUEFALSE
5 TRUEFALSEFALSE
6FALSE TRUEFALSE
7 TRUEFALSEFALSE
8FALSEFALSETRUE
9FALSEFALSETRUE
10FALSEFALSETRUE

View 4 Replies View Related

Quickly Select Cells In Alternating Rows

Jul 21, 2009

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?

View 9 Replies View Related

Conditional Formatting Similar To Shading Alternating Rows

Aug 18, 2006

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.

View 10 Replies View Related

Color Rows With A Macro

Oct 16, 2008

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

View 14 Replies View Related

Macro Script To Delete All Rows Hidden Using Conditional Formatting Color

Oct 6, 2011

After red filling certain cells based on some conditional formatting, I apply a filter to hide them. Now I need to delete them using a macro script.

View 9 Replies View Related

Alternate The Background Color (fill Color) Of Rows In A Spreadsheet

Aug 19, 2009

how I can alternate the background color (fill color) of rows in a spreadsheet. Say I wanted every other row to be gray starting at row 10.

View 6 Replies View Related

Macro To Color Cells Based On Their Font Color

Jun 18, 2008

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.

View 9 Replies View Related

Alternating Odd And Even Numbers?

Mar 15, 2014

I have a column (k) of 65,000 rows with numbers that are either positive or negative digits. The next column (L) has classified them as either 1 for positive number or 2 for negative.

There may be a run of all positive or all negative numbers or 2 positives and 3 negatives or any combination of positive and negative numbers reading down the column.

Reading down the column (from L1), I need to find any sequence of alternating 1 positive, followed by 1 negative or vice versa.

I am looking for evidence to find any sequence that has more than 2 alternations. (Remembering that positive is 1 and neg is 2)

That is, two or more alternations of a positive followed by a negative or neg followed by a positive.

eg
1
2
1
2

or
2
1
2
1

View 4 Replies View Related

Calculating Alternating Values

Mar 6, 2009

I have a bunch of start End times in columns calculating total time :-

View 12 Replies View Related

Alternating Range Formatting

Sep 13, 2006

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

View 8 Replies View Related

Count Alternating Values In Column?

Aug 25, 2014

I'm sure there's a simple way to do this (I've been trying countif/match formulas but I'm not quite there)! I need to group line items with the same dollar value, WITHIN say 1 cent. If I do a pivot it will group exact matches, but I'm looking for the flexibility to group something within a few cents. So instead of pivots, I was trying to think of a formula that would do what Column A is doing below:

*I manually added Column A, looking for a formula that would group dollar values within 1 cent, assuming I've sorted Column B from smallest to largest:

Column A : Column B
1 : $2.13
1 : $2.14
2 : $3.00
2 : $3.01
2 : $3.01
3 : $4.00

View 4 Replies View Related

Merging 2 Columns Into 1 Alternating Every Other Column

Aug 12, 2010

I'm looking for a way to put data into 1 column from 2 different columns, alternating every other row.

The data looks like this:
A B C
1 2
3 4
5 6

And I want C to look like this:
C
1
2
3
4
5
6

I don't necessarily need to keep the data in columns A and B, as long as C follows this format.

Excellent, just what I was looking for. I couldn't get the UDF working right, I'll try messing around with it, but the Index solution works great!

View 5 Replies View Related

Sort Column In Alternating Manner

Mar 13, 2014

I have sets of data... like so:

A word text blablabla
B word text blablabla
A dog cat france
B dog cat france
B this that other
A this that other
A tiger dragon monkey
B tiger dragon monkey

My source data I can't get to sort alternating on columns A and B, and I would like the data to alternate A-B-A-B-A-B while retaining the pairings of data in the adjacent fields. Is this possible?

View 1 Replies View Related

Highlight Duplicated With Alternating Colors

Feb 21, 2014

I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.

What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.

My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.

I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?

Code:
Sub Dups()
Dim Rng As Range
Dim CL As Range

Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))

For Each CL In Rng
If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then
CL.Interior.ColorIndex = 6
End If
Next CL
End Sub

View 1 Replies View Related

Adding Alternating Cells In Long List?

Jan 4, 2014

how to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).

View 2 Replies View Related

Select Alternating Columns With Unknown End Point

Feb 18, 2010

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.

View 7 Replies View Related

VBA Match Function With Variables / Number And Alternating Ranges

Jun 2, 2014

I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.

ExampleFile.xlsm

View 13 Replies View Related

Separate A Line Of Alternating Text/Numbers Into Columns

Jul 20, 2009

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?

View 9 Replies View Related

Excel 2010 :: Highlight Groups Of Identical Number Cells In Alternating Grey And White?

Jun 27, 2014

I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.

1

1

2

3

4

[code].....

View 4 Replies View Related

Color Only Even Rows

Aug 31, 2006

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'

View 9 Replies View Related

Color Blank Rows

Aug 16, 2008

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.

View 14 Replies View Related

Find Rows By Color

Jul 14, 2009

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........

View 14 Replies View Related

Changing Color Of Rows

Mar 14, 2008

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.

View 9 Replies View Related

How To Automatically Color Highlight The Rows

Apr 1, 2014

I am working on a very long worksheet and creating a macro to automatically highlight the row that I am working on

Ex: If I am on row 2 I can use a shortcut to highlight it in blue when I am done working on it I can use another short cut to remove the highlight

Or if I am working on row 5 and 6 I can highlight 2rows or more but still able to remove the color after.

I am wondering if I can have different shortcuts for color blue, green and yellow and another shortcut for removing it.

View 6 Replies View Related

Auto Color Shading Of Rows

Oct 24, 2005

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.

View 9 Replies View Related

Cut And Paste Rows Based On Color

Dec 21, 2009

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.

View 9 Replies View Related

Fill Alternate Rows With A Color

Jul 18, 2006

is there any easy way to fill alternate rows of a worksheet with a particular color? I have a worksheet with 175 rows and alternate rows are to be filled with green!

View 4 Replies View Related

Color Rows Based On Row Citeria

Nov 29, 2006

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)

Data (Action)
* * * * * * 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

View 9 Replies View Related

Hide Rows Based On Color

Apr 4, 2008

I have the following code to hide rows based upon whether or not the cell in column A is black or not.

Sub hide_black()

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
End If
Next MyCell
End Sub

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.

View 6 Replies View Related

Remove/Delete Rows By Color

May 7, 2008

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" ...

View 3 Replies View Related

Hiding Rows That Have No Fill Color In Any Of Columns?

Apr 15, 2014

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.

View 4 Replies View Related

How To Fill Color For Multiple Rows And Columns

May 21, 2014

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.

View 7 Replies View Related

Delete Rows Based On Fill Color

Aug 21, 2008

i found a link to my problem in the subject title below,

http://www.ozgrid.com/forum/showthre...t=45580&page=2

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.

View 9 Replies View Related

Hide Blank Rows - Non Text/color

Dec 28, 2009

I received this code which hides blank rows within a range.

View 12 Replies View Related

Filter Rows Based On Background Color

Oct 27, 2008

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?

View 9 Replies View Related

ADVERTISEMENT