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


ADVERTISEMENT

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

Excel 2010 :: Conditional Formatting - Shading By Group When Filtered?

Sep 19, 2013

I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:

Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1

STATES (unfiltered)
AL
AL
AK
AK
AK
AR
AR
AZ
AZ
AZ

When I apply a filter, the rows remain shaded as they were originally:

STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ

I want the conditional format to change when I have filtered out items (DESIRED RESULT):

STATES (Filter OUT AK)
AL
AL
AR
AR
AZ
AZ
AZ

I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.

View 2 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

Conditional Format Multiple Shading

May 21, 2008

In a workbook I have

Sheet 1 and Sheet 1a
1a is used to provide a start and finish date for a task - there are many of them
The following is located in a cell in sheet 1 which looks at 1a for the dates etc

HTML =IF(AND($A$427+B$430-1>='1a'!E10,$A$427+B$430-1<='1a'!I10,B$429<>"S"),"X","")

The end result is an X located in the respective feilds.
This works fine but.
I have set up the Calendar area in 1 so that when the X is shown it is Red.
The sheet also shows a Blank in both the SS [Sat and Sun]

What I would like to do is to have instead of the blank in the SS [Sat & Sun] a shaded grey which would clearly indicate that this was weekend.

What I am after is the weekdays with the X as now and the weekend only in a grey shade as it is at the moment without the X

View 14 Replies View Related

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

Conditional Format Shading Stop Updating Until Save...?

Aug 17, 2009

I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?

View 3 Replies View Related

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?

View 10 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

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

Conditional Format: Alternate Row Shading Based On First To Letters In The A Column

Oct 27, 2008

I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.

The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.

For instance:

Column A

CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4

CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.

MacOffice Excel 08 is used so no VB code can be used.

View 9 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 For Two Rows

Feb 15, 2012

I have a spreadsheet with 10 columns and 8000 rows. column 2 contains names and 3 contains districts of residence.

I need to highlight (or delete) whole rows of duplicate entries in the name column(2) that are in the same district (column 3).

Traditional conditional formatting highlights all duplicates. I just need those duplicates in column 2 that have the same text in column 3.

View 2 Replies View Related

Getting Conditional Formatting Rows?

Jun 7, 2012

I have rows of information linked to other sheets with forms to fill out (master sheet). I want to be able to have a red fill if one of the cells is blank, which I do have working now, but I want a clear row if the "indicator cell" is blank.

View 1 Replies View Related

Retaining Conditional Formatting On New Rows?

Jul 16, 2013

I have a spreadsheet for recording property maintenance. All properties are grouped by a city zone in a specific colour, and there is conditional formatting on this. Also drop down lists for properties, contractors etc.

This all worked well, until I needed to start inserting rows. All the conditional formatting and lists didn't replicate on the newly inserted rows.

Also, we need to assign a unique reference number to each row but currently manually inputting them - very inefficient way of doing this as they will inevitably get mixed up and confused as rows are removed.

Spreadsheet.jpg

View 9 Replies View Related

Conditional Formatting For Multiple Rows

Jul 8, 2009

I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.

View 4 Replies View Related

Copy Conditional Formatting To Other Rows

Jan 15, 2010

I'm running a spreadsheet at the moment in 2007, and one of the funcitons I've made it for certain cells to either change format if the number is to big or to small depending on the cell it is.

I have formatted all for one line against duplicate scores on the same line.
The problem is this is time consuming and there are 22 different lines.
Because with Conditional Formating you can't simply drag like you can with formulas, I was wondering if there was a more simple approach. I have demonstrated the formating required on Row 6, and require the same formating on rows 8:10, 14:26, 32:36.

Also, just to make things complicated, the values change for each line.
I have attatched the file. Row 6 with the label DEMO LINE contains all the correct formating I require.

View 3 Replies View Related

Insert Rows Through Conditional Formatting

May 15, 2013

Doc1.docx

I need the Left picture to look like the Right picture... There's over 30,000 lines of data.

View 3 Replies View Related

Conditional Formatting On Different Rows With Different Query

Aug 20, 2013

I have the following data:

C D E F G
Jan Feb March Q1 TargetThreshhold
£ 2,067 £ 1,978 £ 2,161 £ 2,100 £ 2,310

£ 1,492 £ 1,422 £ 1,611 £ 1,600 £ 1,760
£ 2,718 £ 2,683 £ 3,263 £ 2,400 £ 2,640
£ 2,853 £ 2,335 £ 2,481 £ 2,300 £ 2,530
£ 1,412 £ 1,533 £ 1,526 £ 1,600 £ 1,760
£ 1,067 £ 1,133 £ 1,246 £ 1,400 £ 1,540

And I am wanting to use conditional formatting. But wondering if there is an easier way than what I have at the moment.

The Formatting im using is:

=$C4>=$G4 Where value is TRUE the cell returns red and this applies at the moment to the First Row.

Is there a way I can get Conditional formatting to recognise the next cells without typing for each set of rows.

aka Row B would Read =$D4>=$G4
Row C would read =$E4>=$G4

etc?

Id rather not have to do a conditional format for each row as I have about 50 rows of data to do this to

View 4 Replies View Related

Conditional Formatting For All Rows In Each Sheet

Dec 4, 2008

I want this macro to apply the conditional formatting to all rows in each sheet that have "BE" in col G. The macro works fine in Cell A2 only. And only on the worksheet that I have active at the time that I run the macro. Can anyone tell me how to fix this?

View 4 Replies View Related

Using Conditional Formatting To Delete Rows

Jul 26, 2007

I have a worksheet that I use Conditional Formatting to check the dates in a total of 5 columns. In those columns, if the date is before 8/1/2006, it changes the cell color to red. Is there a formula I can put into Condition 2 to that would then clear out the whole row if within those 5 columns there isn't a red cell? This would take hours of work off of my slab.

******** ******************** ************************************************************************>Microsoft Excel - Credetnialing Master.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAT1=
ATAUAVAWAXAYAZBA1LicenseExpirationInsuranceExpirationDEA_ExpirationStateStateDrugNumberStateDrugExpirationAttestationDateNPDB_Date212/31/20078/2/20071/31/2009*229312/31/20073/7/20073/26/2007312/31/20075/1/20082/28/2010*253812/31/20077/6/20067/24/2006412/31/20078/26/200712/31/2007*256312/31/200712/12/200612/14/2006512/31/200710/19/200712/31/2009*276812/31/200711/28/200412/8/2004612/31/20079/25/20078/31/2007*303012/31/20074/6/20064/17/2006712/31/200712/3/20077/31/2009*315212/31/20075/3/20055/11/2005812/31/20078/2/20078/31/2008*323612/31/20072/28/20073/26/20079**10/31/2009****7/7/20031012/31/20078/10/20073/31/2008*353512/31/20074/11/20064/24/20061112/31/20072/1/20083/31/2009*365012/31/20
.........................

View 9 Replies View Related

Conditional Formatting (>) - Highlight Rows

Jan 12, 2010

I have data from Col A to Col M, in Col C i have amounts, i need a conditional format that will highlight rows from Col A to M in yellow which are >5,000,000.00 and

View 9 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

Need Conditional Formatting For 2 Columns, Unlimited Rows.

Mar 10, 2009

Im having a little trouble with this, Im dealing with a list, and I need to change the colour of rows, based upon a value in 2 different columns.

Im close, but cant quite get it.

Basically its like this, the data in these cells will only ever be either; blank in one or the other, both are never blank. or a number up to about 3 or 4, usually 1 or 2.

View 2 Replies View Related

Conditional Formatting Multiple Rows Independently

Apr 22, 2009

I have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know. Sample worksheet attached.

View 2 Replies View Related

Conditional Formatting To Change Colour Of Rows

Aug 13, 2009

I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).

View 10 Replies View Related

Conditional Formatting Rows In Pivot Table

Mar 25, 2014

I have a pivot table which shows below... I need to set up a conditional format to highlight the row where all fields are "0"

Company
Deals
Opps
Days since Deal
Days since Opp
Days since Meet

Co 1
1
7
80
20
20

Co 2
0
0
0
0
0

Co 3
1
4
30
30
28

Co 4
3
3
30
30
37

View 1 Replies View Related

Deleting Rows Based On Conditional Formatting

Jun 11, 2009

I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.

Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.

Here's what I already have:

View 10 Replies View Related

Conditional Formatting For Multiple Columns And Rows?

Mar 28, 2014

I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.

I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.

Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved