conditional formatting: separating rows with differing values

[web_stalker2001]

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.

 

[Guest]

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]

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?

 

[Ron Coderre]

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

 

[web_stalker2001]

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.

 

[Ron Coderre]

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