conditional formatting: separating rows with differing values

Hi,

Need help with conditional fomatting.

What I need:
I got a worksheet with 950 rows and 5 columns. The first row contain
the headers. Columns A, B, D and E contains unique values. But the
column C contains text values which repeats sequentially for 6-7 rows
and changes thereafter. Now I want to put up a line (using border)
through the whole row dividing this transition row.

My Idea:
Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so
on till 2 consequtive values differ. Now divide the 2 rows using the
border formatting option in conditional formatting dialog box.

So far...
After selecting the entire worksheet, I have tried each of the below
formulas with no desirable result.
=EXACT("C"&ROW(), "C"&ROW()+1) = FALSE
=EXACT(INDEX(C2:C950, ROW(), 3), INDEX(C2:C950, ROW()+1, 3))
=INDEX(C2:C950, ROW(), 3) <> INDEX(C2:C950, ROW()+1, 3)

Is this possible at all? Thanks for any hint/help.


-- 
web_stalker2001
------------------------------------------------------------------------
web_stalker2001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34203
View this thread: http://www.excelforum.com/showthread.php?threadid=539646

0
5/7/2006 2:27:02 PM
excel 39879 articles. 2 followers. Follow

5 Replies
530 Views

Similar Articles

[PageSpeed] 47

Try this:

Select rows 3 through 950 (the entire rows), with Row_3 as the active row.
<format><conditional formatting>

For an exact case-sensitive test:
Formula is: =NOT(EXACT($C3,$C2))

OR...for a non-case-sensitive test:
Formula is: =($C3<>$C2)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"web_stalker2001" wrote:

> 
> Hi,
> 
> Need help with conditional fomatting.
> 
> What I need:
> I got a worksheet with 950 rows and 5 columns. The first row contain
> the headers. Columns A, B, D and E contains unique values. But the
> column C contains text values which repeats sequentially for 6-7 rows
> and changes thereafter. Now I want to put up a line (using border)
> through the whole row dividing this transition row.
> 
> My Idea:
> Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so
> on till 2 consequtive values differ. Now divide the 2 rows using the
> border formatting option in conditional formatting dialog box.
> 
> So far...
> After selecting the entire worksheet, I have tried each of the below
> formulas with no desirable result.
> =EXACT("C"&ROW(), "C"&ROW()+1) = FALSE
> =EXACT(INDEX(C2:C950, ROW(), 3), INDEX(C2:C950, ROW()+1, 3))
> =INDEX(C2:C950, ROW(), 3) <> INDEX(C2:C950, ROW()+1, 3)
> 
> Is this possible at all? Thanks for any hint/help.
> 
> 
> -- 
> web_stalker2001
> ------------------------------------------------------------------------
> web_stalker2001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34203
> View this thread: http://www.excelforum.com/showthread.php?threadid=539646
> 
> 
0
5/7/2006 2:49:02 PM
Nothing happened. 
I selected the entire rows 3 through 950
Added the following formula in conditonal formatting dialog:
=($C3<>$C2)
Added an option to apply only the lower border with red color if the
condition comes true ie., C3 not equal to C2
Applied the formula - nothing happened.

I tried the same thing with the Formula: =NOT(EXACT($C3,$C2))
Again nothing happened.

Maybe I'm not exactly understanding the following line
> 
> with Row_3 as the active row
> [\quote]
> How can I select multiple rows but keep only a single row active?


-- 
web_stalker2001
------------------------------------------------------------------------
web_stalker2001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34203
View this thread: http://www.excelforum.com/showthread.php?threadid=539646

0
5/8/2006 12:38:24 PM
web_stalker2001:

Regarding: >>with Row_3 as the active row<<

For the Conditional Formatting (CF) to work properly for any given
cell, the formula must be relative to THAT cell.  In your case, you
want to test if the Col_C value for the current row does not equal the
Col_C value for the row above the current row.  

If the active row is Row_3, the CF formula would be: =($C3<>$C2)
You wouldn't want Row_10 comparing the values of cells C3 and C2,
right?

With Row_10 as the active row, this CF formula applies: =($C10<>$C9)

The point of my comment was that the posted CF formula only applies to
cells in Row_3, but the kind of reference used will cause it to
automatically adjust to any other rows that happen to be included in
your selection.  (If you have questions about the dollar signs in the
reference, check "absolute references" in Excel Help.) So....after
selecting the rows to be impacted, notice which row is referenced in
the Name Box (to the left of the formula bar) and adjust the above
formula accordingly.  

I hope that helps.

Regards,
Ron


-- 
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21419
View this thread: http://www.excelforum.com/showthread.php?threadid=539646

0
5/8/2006 1:15:23 PM
Hi Ron,

Thanks for the explanation. Please read my previous post and kindly
tell me what I have to change to get the desired result. I seem to have
followed your instructions properly. This problem is bugging me, making
it work has become an obsession with me.


-- 
web_stalker2001
------------------------------------------------------------------------
web_stalker2001's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34203
View this thread: http://www.excelforum.com/showthread.php?threadid=539646

0
5/9/2006 1:16:22 AM
OK, web_stalker2001

Try this:

Select Row_3
<format><conditional formatting>
Formula is: =($C3<>$C2)
Click the [Format] button...set your format.
Click the [OK] buttons

That should set the CF for Row_3

Next, select the remaining rows
<edit><repeat>

Does that get you where you want to be?

Regards,
Ro

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=53964

0
5/9/2006 2:00:25 AM
Reply:

Similar Artilces:

Trendline values #2
I used the function "LOGEST" to get an exponential fit of a series of numbers, which gives me a value of 1.0610. I also used "RSQ" function to get the R-square, which is 0.9442. Then I made a chart based on the same series of numbers and add trendline to the curve. I selected "Exponential" under "Type" and checked "Display equation on chart" and "Display R-Squared value on chart". The value shown on the chart is "y=3928.8e^0.0592x" and "R^2=0.9104". I expected to see "y=3928.8e^0.0610x" and "R^...

problem carrying over values
I have one form that has a button I''ll call it form1. The button looks at a text box for an ID and if its null it opens form2 and creates a new record in form2. If the text box is NOT null, it will go find the ID that's in the text box and filter it in form2. When the new record is created in form2, I need the ID to be put into the NULL text box in form1. I know how to do this however, if you open form2 on its own, then the error msg comes up that it can't find form1. I know why I'm getting this error, but don't know the fix for it. Is there a work around? Thanks...

Can I make a row "float"?
I would like to make a row float. So if I scroll down to row 211 or whatever, I will have one row tha will always be on top no matter how far down I scroll. Thanks in advance. dvcrogers@sbcglobal.ne -- dvcroger ----------------------------------------------------------------------- dvcrogers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3262 View this thread: http://www.excelforum.com/showthread.php?threadid=52413 Say you want row 4 to freeze Select row 4, then go to window and freez -- davesexce --------------------------------------------------------...

different start time different days
How can I set different start times for different days? Outlook seems to assume I work every day for the same hours, not so. I was hoping to use its shading as a visual guide for different days, but it wants all days to have the same time schedule. -- qpackard http://forums.slipstick.com Outlook doesn't handle shift work. You could make tentative appointments for the work hours to highlight them. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: ...

clash between different versions of excel?
Hi all. I created an excel document using excel2003 and save it in a file server. As it is a shared file, it was opened by another user using excel97 for editing. However after the file was saved by the user of excel97, the layout of the content in the file became very different compared to the 1 that was saved by myself when i was using excel2003. As the user of excel97 merely change the data without editing any layout, why did this happened? Is it due to the different versions of excel being used? How can i solve this? Thks in advance. ...

same subform used in two different forms
Hi Folks, Is it possible to use the same subform in two different main forms simultaneously? If it's possible, is it good practice? I can foresee that there may be problems with code, but the subform I'm thinking of is very basic. Three tables: tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID FirstName MiddleName ....other personal data tblAddress AddressID AdFamID street city state zip Two main forms for different types of data entry frmFamily with family information on the main form and address information...

Excel formatting #3
Could someone please help with a macro or any other suggestion to format an Excel sheet. Basically data is downloaded from a bank every week and the size of the file can vary but the formatting is constant. That is each record is 7 lines. So first record is line1-7, second record is line 8-14, third record is line 15-21 and so on. I need to format this data and basically I can record a macro for one data set that is for the record on line 1 to 7. My question is how can I set it to replicate so that it replicates for line 8-14 and then 15-21 and so on. The source formatting remains the same...

#VALUE! error
I'm sorry but I added to this formula and it started with the #VALUE! again. The error goes away once I enter data. It's just so tacky looking seeing all these error messages. Please help! =IF(OR(B3="",D3="",B3>D3),"",'Tank Sizes'!$F$15)*(2/98)*F3/D3 You have your closing parenthesis for the IF function right after the "$F$15 reference which means the division by D3 is not controlled by the IF function's testing for D3="". I think moving the parenthesis to the end of the calculation will yield the result you want... =IF(O...

Values in the Legend?
Is there a way to show values in the legend - behind the series text? Thanks, Phil Do you want to show just a single value? You can either build a lengthier label (like =A1&B1), and use this as the new series name, or you can select multiple cells for the name, and Excel puts them together with a space between. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Phil Hageman wrote: > Is there a way to show values in the legend - behind the series text? > Thanks, Phil ...

Adding on to a formula across rows and columns
I need to add a second section of a formula onto an existing formul across rows and columns. The problems are: -Not all cells currently contain formulas (some are blank) -Not all the formulas are the same (there are at least 2 different formulas among all the existing formulas that I want to add that secon formula or section to)...and the different formulas are somewha interspersed. -Some of the existing formulas contain relative references (I want t change them to absolute references) -If I use relative references to somehow copy a portion of a formul across all remaining rows and columns,...

Separating data into new sheet
Hi guys, I have the following data on worksheet "Sheet1" Code Value A 1000 B 200 A 100 C 150 B 300 B 200 C 400 I have worksheet "Sheet2" that I want user to input what CODE they want to IMPORT... Let say A then on "Sheet2" i would get: Code Value A 1000 A 100 It is a combination of sorting, copying and moving the wanted data to the targeted sheet. Is there an excel feature/solution that does this? or a VB code is needed? Thanks in advance ...

Conditional Cell Category
Is it possible to make a cell's category conditional on the value in another cell? I would like to have a cell display its value as a currency if the cell before it says "Monthly", and as a percentage if the cell before it says "Annual". I am working in Excel 2003. Thank you, Trey ~ On 29 June, 15:39, one third <g...@hotmail.com> wrote: > Is it possible to make a cell's category conditional on the value in > another cell? > > I would like to have a cell display its value as a currency if the > cell before it says "Monthly", and as ...

maximum number of rows that can be coppied to clipboard
is there a maximum number of rows that can be copied to clipboard from a table? using access 2002 on XP, Roland wrote: > is there a maximum number of rows that can be copied to clipboard from a > table? using access 2002 on XP, I have Access 2003 when I tried to copy a table with 70,000 records it said the limit was about 65,000. gls858 65536 -- Regards - Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy "Roland" <Roland@discussions.microsoft.com> wrote in message news:CF98F875-56C6-4C22-8695-D8CFBE47D12E@microsoft.com... > is there a...

What Excel macro puts data in the NEXT row of a sheet?
I have a list of data in col B of sheet 2 which I need to copy and transpose into sheet 1, then delete the data in sheet 2 to use the form again to copy and transpose onto the next line in sheet 1. I have tried with a macro shortcut I've called ctrl + e. (I am really using the data form process but cannot because I have too many inputs). Is there a simple macro I can copy and paste? ...

How to see if different workbooks are linked?
How can someone determine if two workbooks are linked? I can see it is I find the cell, but is there a way to see it at a glance? Thanks, Tracy Hi Tracy You can use Edit>Links in the menubar(not in all excel versions) If it is gray there are no links -- Regards Ron de Bruin http://www.rondebruin.nl "Tracy@Saunatec" <Tracy@Saunatec@discussions.microsoft.com> wrote in message news:50853AE6-A43E-49B2-9E7B-C3C5ECF4C5A7@microsoft.com... > How can someone determine if two workbooks are linked? I can see it is I find > the cell, but is there a way to see it at a glan...

Min and Max values for different salespeople?
Hi there, I am trying to find the minimum and maximum sales for each salesperson. I can not sort by salesperson -- I must sort by sales date. How do I do this? 1/2/1999 Bob $7 1/5/1999 Rick $20 1/7/1999 Sue $75 1/10/1999 Bob $5 1/15/1999 Sue $3 1/27/1999 Rick $53 So, I need to know Bob's minimum sale was $5, and his maximum sale was $7. I know how to use the MIN and MAX functions, but how do I tie in the sales person name? Assume your salesperson names are in column B and their corresponding sale amount is in column C: =MAX(IF(B1:B10="Bob&...

want to turn PT row data into column data
original data: name win loss score paul 1 325 Jane 0 1 225 walt 1 400 nancy 0 200 jane 1 300 nancy 1 200 paul 1 365 walt 1 350 when i create p.t. I get (or a vague approximation) paul win 2 loss 0 avg 345 jane win 1 loss 1 avg 260 walt win 1 loss 1 avg 375 nancy win 0 loss 2 avg 200 but this is what I would like: name win loss avg Paul 2 0 345 Jane 1 1 260 walt 1 1 375 nancy 0 2 200 ---------------------------- HOw? tx phynkel, Grab the g...

Only Display records before and after a change in teh value of a f
I have a table that looks like the following RUNID | DATETIME | PROCESS 0001 1/1/09 1201 Neck 0001 1/1/09 1202 Neck 0001 1/1/09 1203 Neck 0001 1/1/09 1204 Neck 0001 1/1/09 1205 Neck 0001 1/1/09 1206 Body 0001 1/1/09 1207 Body 0001 1/1/09 1208 Body 0001 1/1/09 1209 Body 0001 1/1/09 1210 Neck 0001 1/1/09 1211 Neck 0001 1/1/09 1212 Neck 0001 1/1/09 1213 Neck 0001 1/1/09 1214 Body 0001 1/1/09 1215 Body 0001 ...

Don't chart X-axis without values.
On a line chart I am plotting two individual columns and then SUM(ming) these two for a total. Future dates of the individual columns have no data BUT the formula for SUM(ming) the two results in zero (0) which is plotted on my chart. I've tried a number of approches to the formula, blank (""), and Null (which results in a string and causes an error. How can I leave my formula in the future dates and not effect my chart. Obviously, with moving from 300k to zero in one week is scary. Thanks, Hank Hank - Excel can't return a NULL or BLANK from a function (not t...

formulas when there may/may not be a value
What formula do you use when there may not be a value there? Like: ITEM QTY SOLD T0887 50 1 T0887 100 T0887 200 2 T0992 50 3 T0992 100 To get: # of sold items for each item? -- Nicki Taylor =SUMIF(A:A,"T0887",C:C) -- HTH RP (remove nothere from the email address if mailing direct) "NTaylor" <NTaylor@discussions.microsoft.com> wrote in message news:6E22EEF1-44E5-4C5B-AFBD-B8053172702F@microsoft.com... > What formula do you use when there may not be a value there? Like: > > ITEM QTY SOLD > T0887 50...

combine hide row macro with conditional formatting
I'm sure one of you gurus can solve this in a flash. I've recorded a simple macro to hide a row (Alt O, Alt R, Alt H) but I want it to run automatically based on a cell value. For example: If cell value "A1" is "x" then I want the macro to run, and repeat itself down the column "A2, A3" etcetera until it reaches a blank cell. Is there a way to plug my existing macro "hiderow" into Conditional Formatting for a column; or is there a simpler solution? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.c...

%s in Accounting Format
I am using Excel 2007. I want to have a column that lists percentages - but to still be able to use the accounting single underline correctly. When I manipulate the accounting format to _(*#,##0.00%_) the underlining is not correct. If I use the accounting format I cannot get "%" signs. Thanks. Does this work? _(* 0.00%_);_(* (0.00%) -- Regards, Peo Sjoblom "jadeB" <jbschipper@gmail.com> wrote in message news:a9dfe12b-3c2f-4302-ba49-2c2dd39240c2@33g2000yqm.googlegroups.com... >I am using Excel 2007. > > I want to have a column that lists percen...

Autofit row height in only some rows
Is it possible to change this macro to only autofit the row height on rows 254-332 in the workbook? I am using this macro: Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows.AutoFit Sh.Rows("1.33").AutoFit Application.EnableEvents = True EndSub Thanks in advance for your help. VBA Code: -------------------- Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.EnableEvents = False Sh.Rows("254:332").AutoFit Application.EnableEvents = True ...

Get Dictionary value
Hello, I am just wondering: is there another way to get a IDictionary value using: String value = config["key"].ToString(); Is does not feel C# to have something like config["key"]. But that's just me .... Thanks, Miguel shapper wrote: > Hello, > > I am just wondering: is there another way to get a IDictionary value > using: > > String value = config["key"].ToString(); > > Is does not feel C# to have something like config["key"]. > > But that's just me .... Probably. But, if you r...

Field whose value increments automatically
Is it possible to create a field which automatically increments by 1 every time I open the worksheet? Thanks... Only with code. Use the Workbook_Open event procedure to increment a value in a cell. For example, put the following code in the ThisWorkbook code module: Private Sub Workbook_Open() With ThisWorkbook.Worksheets("Sheet1").Range("A1") .Value = .Value + 1 End With End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinnboy" <Quinnboy@discussions.microsoft.com> wro...