conditional format from row to row

Hi, I am using Excel 2003 and would like a conditional format to work with a 
named range, is that possible?  I have a named Range called City in column b. 
When the city changes I would like for the entire row to change to a specific 
color I designate. I have used this formula =$b16<>$b15 and this works until 
I filter the data. Please if anyone has done this or something close I would 
like to see your formula.

Thanks,
John
0
John1063 (943)
8/13/2009 8:10:02 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
742 Views

Similar Articles

[PageSpeed] 21

For a "2 color" band...one group of rows will be the color you select. The 
next group of rows will not be colored in effect giving you a 2 color band.

Let's assume the range you want to format is A2:B16. You want to color band 
the range when there is a change in column B.

A1:B1 are the column headers.

Select the range A2:B16
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right

=MOD(SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B2,ROW(B$2:B2)-ROW(B$2),0,1)),--($B$1:$B1<>$B$2:$B2)),2)

Click the Format button
Select the desired fill color
OK out

This will be slow to calculate on large amounts of data.

Sample file available on request

-- 
Biff
Microsoft Excel MVP


"John" <John@discussions.microsoft.com> wrote in message 
news:415E40C5-B1A3-4B7E-AFFC-CA15940E8D8D@microsoft.com...
> Hi, I am using Excel 2003 and would like a conditional format to work with 
> a
> named range, is that possible?  I have a named Range called City in column 
> b.
> When the city changes I would like for the entire row to change to a 
> specific
> color I designate. I have used this formula =$b16<>$b15 and this works 
> until
> I filter the data. Please if anyone has done this or something close I 
> would
> like to see your formula.
>
> Thanks,
> John 


0
biffinpitt (3172)
8/14/2009 3:52:28 AM
Reply:

Similar Artilces:

Excel cell format #2
how can i display preceding zeros in excel without formatting as text? In article <5CBDC357-B0B2-49C2-906C-73E94C6172B9@microsoft.com>, "rockfam8" <rockfam8@discussions.microsoft.com> wrote: > how can i display preceding zeros in excel without formatting as text? Precede your entry with an apostrophe. For example... '012345 Hope this helps! Or give it a custom format like 00000 (as many 0's as you need) rockfam8 wrote: > > how can i display preceding zeros in excel without formatting as text? -- Dave Peterson ...

Addiction with conditions
I'm a Excel newbie. And i also don't speak wery well english! :( Sorry. This is my question: I want to made an addiction between some numbers, but i want to declar a condition. There is a function to do this, in italian is "SOMMA.SE" i thik i inglish is somethig similar: addiction.if But the thing that i want is that the conditions are the differen colors of the cells. I hope that someone has comprised to me in my explanation. Thank you! Sergio ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages...

Vlookup with If condition
A1 A2 A3 1 3 4 2 5 6 3 6 3 4 7 8 5 3 2 6 2 1 7 3 9 I am trying to find a combination formula that looks at column A2 for a specific value from another cell (say the cell value is 3 in this case). If column A2 though contains the value "3" as this examples shows, I want to look at column A3 just for the rows containing "3" in column A2 and return the value found in column A1 for the largest value found in A3 for that same row. I hope this makes sense. Thanks for your help. Try using thi...

Number formats and fields
Hi All! I have a list of equations in a word document that I need to add a reference (the first equation in the document will be equation A, the second will be equation B, etc). So I need a numbering field. However, when I get to equation 28 I want the format to be AB, then 29 to be AC, not BB, CC like the default A, B, C… numbering gives you. However, I will be adding other equations randomly throughout the document and referring to the equation reference in text paragraphs, so I would like them to auto update when I add another equation in the middle of the document. Doe...

date format conversion
I have a date of 1/5/2006, it needs to be 01/05/2006. I ma having a hard time getting hte leading 0's where needed. Appreciate any help! -- Buck That sounds like a matter of FORMAT. The date itself, as 'numerical' value sounds fine. You can force a format (to display a STRING that represents a date, but *is* a string, not a date_time value) with the use of Format function: ? Format( #1/2/3# , "mm\/dd\/yyyy") 01/02/2003 Hoping it may help, Vanderghast, Access MVP "buckpeace" <buckpeace@discussions.microsoft.com> wrote in message news:01C568...

Different formats within the same cell
Hello, i want to enter a word in a cell but only have one of the letter fomatted bold, eg - "wo*r*d" Is this possible -- Message posted from http://www.ExcelForum.com Hi, Yes. Enter your word into the cell, go to the formulae bar, highlight th letter you want to have in bold and then click on the bold icon. This will format just that letter to bold -- Message posted from http://www.ExcelForum.com sure. Just edit the word>highlight the letter(s) and change the font,bold,color etc. will NOT work withing a formula. -- Don Guillett SalesAid Software donaldb@281.com "...

File Formate
I have five Excel files which contain the records of my previous 4 years Business matters. Suddenly yesterday when I opned one of my files I received the following messege. --------------------------------------- The file is not in recognizable formate. if you know the file is from another program which is incompatible with Microsoft Excel, click Cancel, then open the file in its original application. If you want to open the file later in Microsoft Excel, save it in a format that is compatible, such as text format. If you suspect the file is damaged, click help for more information about...

Formatting a date in client report
How do I format a date field in a client side Microsoft report (rdlc)? I'm using VS2008, 9.0.30729.1 sp Windows forms project. Unformatted, the dates in my report are displayed like this: 11/19/09 00:00:00 I want to display just the date, not the zeroes. If I enter an expression in the value property for the report textbox like this: =Format(Fields!StartDate.Value, "D") (as shown in http://msdn.microsoft.com/en-us/library/ms251668.aspx) the report will display "D" instead of the formatted date. The same thing happens with other format string...

Exporting auto format
I am exporting from Access to an Excel spreadsheet but I cant seem to stop the auto conversion that takes place. When I open the Excel spreadsheet the data is all different. E.g. I have a fund number of "8E0" and the cell shows 8 and not the correct data. It's fustrating for the datatype in Access is text and should stay the same in Excel. The version I have in Access and Excel is 2002 SP-2 Any Ideas how to stop the auto conversion? I can do it by cut/paste of the query but I am trying to export via the report output itself to Excel. Thanks --- Message posted from http://www...

Average Row Formula
Is there a way to calculate the average of a row if some cells are empty but when calculating the average they should have the value of the cell to the left? If the following data is enterered A B C D E F G 1 3 5 6 For the calculation the cells should use the data A B C D E F G 1 3 3 3 5 5 6 Also what would the average formula be if you wanted to skip one of the cells from the average calculation? Thanks Tom pls do NOT multipost. It wastes resources -- Don Guillett SalesAid Software donaldb@281.com "Tom" <tsanders123@hotmail.com> wrote in message news:111546...

Date when I last change cell in a row?
Please, Help. In my database, every row represents a separate client. I wonder if it is possible to input the date when I last changed any value in a particular row. This way I will know when exactly I last updated the information for client in row 4, 5 etc. I don't need the date when the document was last saved. Please, let me know if it can be done in Excel. Thank you, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Row = 5 Then Me.Range("H1").Value = Date ...

Macro to return cursor to specific column same row
Is there a simple way of returning the cursor to a specific column (say column "B") from any column, but keeping it in the same row? try cells(activecell.row,2).selec -- Kiera Born ignorant and still perfecting the ar ----------------------------------------------------------------------- Kieran's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=124 View this thread: http://www.excelforum.com/showthread.php?threadid=31864 Magic! Kieran - you're a champ! THANK YOU. "Kieran" wrote: > > try cells(activecell.row,2).select > &g...

formatted values from single equation
I would really like to have a Excel spreadsheet with two worksheets, the first sheet has raw data (16 across, 100's down) and what I would like to do on the second sheet is add to the first row, 16 equations (lets say for now y=x). then underneath this the formatted data where x is cell A1 on the first worksheet and y is on the second worksheet. Obviously you would just normally in each cell on worksheet 2 type the equation in (='worksheet1'!A1), but I would like to show people the equation being used. Is this possible, does this message make any sense?? Nice -- mr_nice! ---...

Formating in a Report, the highest 3 records
How do i can format the hightes 3 records on an Access Report? Unfortunately the Access doesn't have the Excel have the function "Large"... Can you help me? Look in Access Help for "TOP". HTH -- -Larry- -- "Luis Marques" <Luis Marques@discussions.microsoft.com> wrote in message news:F467B234-EA95-4F79-91B0-EDC12E61FD00@microsoft.com... > How do i can format the hightes 3 records on an Access Report? > > Unfortunately the Access doesn't have the Excel have the function "Large"... > > Can you help me? Try writing your ...

Finding numbers in rows and highlighting them
Hi everybody :) I need help. This is a lotto system. I have a spreadsheet with 508 rows and 45 columns. In each row there are 45 numbers mixed (1 to 45). I need a script or some other way to find where particular (drawn) numbers are in each of the 508 rows. If my explanation is not clear please ask for farther clarification. Thank you in advance for your help. Joh -- Johncobb4 ----------------------------------------------------------------------- Johncobb45's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1658 View this thread: http://www.excelforum.com/showt...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

Two rows comparison
Folks, In my VBA programming New issue appeard. I was asked to create "simple" macro that compares two rows - in two different worksheets cell by cell, and if the value in two cells is the same in other row o worksheet 2 certain value should be placed. Is there anybody who ca help me. thanks a lot greetings skite -- Message posted from http://www.ExcelForum.com Skitek, We need a little more information: how are the rows determined, and how are the sheets determined? Are they the same row number of each sheet, or is there a key number in one column that must be matched. And ...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Coping row by selecting rows with mouse in macro
Just tring to write a macro to copy one row to another with some columns changed. Can't figure how to mouse pick the second row, don't want to key in. I can enter macro with one line selected but can't mouse pick destination row. Need Help! Thank you. You can use a InputBox Type 8 to select your range something like this Sub test() Set RngCopy = Application.InputBox("Select the range to copy", Type:=8) Set RngPaste = Application.InputBox("Select the range to paste to", Type:=8) RngPaste.Value = RngCopy.Value End Sub -- Paul B Always backup your data before ...

Outlook 2003 HTML formatting problem
After upgrading clients to Outlook 2003 we've started to experience a strange problem with emails formatted in HTML. The scenario goes like this. 1. A local user creates an HTML formatted email and sends it to an external recipient. 2. The external recipient replies to the original message. 3. Originator replies to the reply 4. The external recipient receives the HTML formatted email with portions of the text grossly enlarged. 5. Upon review, it would appear that the external HTML client dropped a (.) changing the font size from 10.0 to 100 causing the enlarged text. Editing the HT...

help: convert ms access from FAT format to NT format
I need help on converting Microsoft access from win98 FAT format to WinXP home NT access format. I recently bought an off-lease IBM for $299, loaded with winXP home and office 2000. I activated it and thought I could easily convert my win98 office 2000 access files. Unfortunately, that wasn't the case. It kept telling me it couldn't convert the old files I copied form win98 HD. Please help. As Joseph explained elsewhere, this makes absolutely no sense. What version of Access was used to produce the old files. What did you do to get them onto your new machine? How are you ...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

Problem: last row not filtered
I have a spreadsheet, with a list of records approx 350 rows x 75 columns, on which I regularly use autofilter on several columns. It has been in use for about three years, initially without problems but recently I have found that last row is never filtered out, regardless of the filter criteria used. The row immediately after is blank, if I use the select current area button (or macro command) the last row is included. The spreadsheet was developed and is usually run on Excel 97 but it also occurs under Excel 2003. It is regularly run by myself and several colleagues, on various machines, all...

Reference Cell in custom format????
I am trying to reference another cell in the custom format area. how it is seet up currently, I have a column (A) that will have the quantity, column (B) has the unit of the quantity, and column (C) has the unit price, followed by column (D) that will have the total cost of the object (i.e. column A * C = D) I am trying to have column C set up so all I have to do is type the unit price but the custom format of the cell will change the value to include the label that is in the column next to it (B).......currently for examples sake, I am trying to set the unit price to 50, and the unit o...

Custom currency formats
Two questions. 1) I have two PC running Excel 2002, call them Old and New. I am phasing out Old and it has Excel 2002 while New has Excel 2002 SR2. If on the Old machine I use Format|Cells, Currency then the Symbol box includes Francs (French). This and all the 'old' currencies of EU countries that have adopted the euro are missing in Excel 2002 SR2 on the New machine. Did they go with SR1 or SR2? 2) I know how to make a custom format for, say francs using #,##0.00 "F". But in an experiment I took a file from Old with a cell displaying 123.56 F and opened in New. It displaye...