shading different rows when a value changes in col.

[Guest]

Hi,

I am trying to change the shading value of a row every time the value
changes in a particular coloum. This could be after 1 row or 20 rows.

cheers.

 

[Govind]

Hi,

Try using Format->Conditional formatting to achieve the same.

Govind.

 

[Guest]

Hi Linc
Have a look at Format / Conditional Formatting
and use the Formula is option.
eg, Highlight say, A1 to C1 and then select Conditional formatting
select formula is, and then put in =D1=20, then set your formats.
When you put 20 in D1, the other cells will change to your format condition.

HTH
Michael Mitchelson

 

[Guest]

Thanks Michael and Govind,

I have tried the conditional formatting area but have been unsucessful. I
not quite sure how to explain but will give it a go.

1
1
1
2
3
4
4
4

I want to write a formula so that every time the value in the acending
coloum changes, shading toggles on or off. So, using the numbers above, the
end result I'm trying to achive is that the row containing 1 would shade, 2
wouldn't, 3 would and four wouldn't.
I have about 1.5 thousand lines to alternatly shade so it will take ages by
hand each day.
Hope this explains it better.

cheers,

Linc

 

[Guest]

Linc
Highlight all of the cells you want to alternate highlight and then
In your Conditional formatting formula, put the following
=MOD(ROW(),2)=0

HTH
Michael Mitchelson

 

[David McRitchie]

Hi Linc,
see http://www.mvps.org/dmcritchie/excel/condfmt.htm

Place cursor in A2
select all cells Ctrl+A (or if you have Excel 2003 Ctrl+Shift+Spacebar)
A2 is your active cell, which is imporatant because your C.F. formula is based on the activecell
Format, Conditional Formatting
condition 1 -- Formula is: =$A2<>$A1
press the Format button, patterns, choose a pale pastel colori

What this actually means is that the is one conditional formatting formula
that is applied to all cells on the worksheet (selection), The $A means
that that column is the column that will be checked on each row (each cell).

 

[Guest]

Thanks Michael,

That will highlight every second row but not a varying number of row before
toggling. Is there someway I can i can get it to check if its the same value
as the previous cell and toggle if it is not?

cheers,

Linc

 

[David McRitchie]

That will actually color every other row (the EVEN rows) , which is not what
the poster asked for, but color banding is definitely one of the advantages of
Conditional Formatting. You probably have seen Chip Pearson's
page on the subject: http://www.cpearson.com/excel/banding.htm

 

[Guest]

Hi David,

This almost worked. It seems to have highlighted the last row of any rows
with the same values as well as any rows that are the only ones of their
value.

 

[David McRitchie]

Sorry about that, try this:

Color all unique rows (based on column A) with a light pastel color, and
Color the first row (based on Column A) of groups of rows with same value with a different pastel color.

Select cell A1
Select all cells, Ctrl+A
The active cell must be on Row 1 because formula is based on $A1 as reference

Condition 1 -- Formula is: =AND($A1<>OFFSET($A1,-1,0),$A1<>OFFSET($A1,1,0))
Condition 2 -- Formula is: =$A1<>OFFSET($A1,-1,0)

The first condition will identify items that are unique (single item grouping)
The second condition will identify the first item of multiple item groups

To eliminate highlighting of the empty cells at end use
Condition 1 -- Formula is: =AND($A1<>OFFSET($A1,-1,0),$A1<>OFFSET($A1,1,0))
Condition 2 -- Formula is: =AND(NOT(ISBLANK($A1)),$A1<>OFFSET($A1,-1,0))

If I misunderstood and it is okay to highlight the unque rows as well, use
(** this is the one that I would use**)
Condition 1 -- Formula is: =AND(NOT(ISBLANK($A1)),$A1<>OFFSET($A1,-1,0))

None of the above will color row 1 you could fix that by assuming Row 1
would always be the start of a group, including a group of 1 and that you
have no header row.
Condition 1 -- Formula is:
=OR(ROW($A1)=1,AND(NOT(ISBLANK($A1)),$A1<>OFFSET($A1,-1,0)))

The comparison for the cell before Row 1 will result in an Error,
which is not a True condition so coloring would not be applied on an error
unless you add the OR condition.
--

 

[Guest]

Hi David,

Thanks very much for the help, I appreciate it.

Unfortunatly this still does not achieve the desired result.
My technical knowledge of excel has been well exceded by now so I cannot
give a technical response. I will try to re-explain.

In the data below, I am trying to make the rows with the following entries
in the second col. highlight. as1, 3dfg, sf5, ed7, 9rgf, nh11. This should be
every time the cell value changes, so does the highlighting.
I know the help you have been giving me works on the first col being the
values used, which I have tested, but the data dump will put this in the
second col.

Hope you can help.
cheers, Linc

Customer Name Sales Order Number Cust P/O or W/O Number
x as1 1
x as2 2
x 3dfg 3
x gf4 4
y sf5 5
y sf5 6
y sf5 7
y cbvg6 8
z ed7 9
z ed7 10
z ed7 11
z ed7 12
x ed7 13
x ed7 14
x ed7 15
x ed7 16
y vf8 17
y 9rgf 18
y qw10 19
y qw10 20
z qw10 21
z qw10 22
z qw10 23
x nh11 24
x re12 25
x re12 26

 

[David McRitchie]

Your expectations do not match your data , because Row 1 is unique
and the other unique rows you want to ignore. Such discrepancies make
debugging very difficult. But you did show me that Row 1 is not
always going to colored because it would only get colored if row 2 has the
same value.

Your selection will determine which cells get colored, it you want to color the
entire row then select all cell. If you just want to color within Column B then
select column B only.

Assuming we are checking Column B then this is the formula (make the window as wide as possible)
copy this formula for the Formula 1 condition -- no other conditions
=AND(NOT(ISBLANK($B1)), IF(ISERR($B1<>OFFSET($B1,-1,0)),1,$B1<>OFFSET($B1,-1,0)), $B1=OFFSET($B1,1,0) ).

If it is not column B you are checking then change the column to the column that is being checked.

When you enter the formula, the active cell must be on row 1.

 

[David McRitchie]

there is not supposed to be a period at the end of my formula

 

[Guest]

Hi agian,

This has higlighted the first row of all entries that have multiple rows
with the same value in the B col.

I'm sorry if I have not explained my self very well and I'm happy to let it
slide if it is to difficult to work out in this fashion.

cheers,

Linc

 

[David McRitchie]

isn't that what you wanted. I indicated variations.

What you select is eligible for coloring, because logically the formula
will be in every cell that is selected when you enter the conditional formatting.
..
If only cells in one column are to be colored then only select that column.

The formula is checking only column B if it is supposed to check values
in a different column then change B to that column.

 

[Guest]

Is it alright if I email a small sample to you?
I have your email address.

 

[David McRitchie]

as long as you mark what it is supposed to look like

 

[Guest]

Sent

 

[David McRitchie]

What was wanted was a Irregular Color Banding, Color Grouping with Conditional Formatting

which in appearance is somewhat similar to Color Banding on Chip Pearson's site
http://www.cpearson.com/excel/banding.htm
except the areas will be dependent on the data and irregular number of rows.

If the column to be checked is B and the existing data is in A through C
then a helper column can be created in column D

D1: 0
D2: =MOD($D1+($B1<>$B2),2)

so that we will have 0 or 1, the rows with 1's will be colored by Conditional Formatting.

but that formula would not work if the data were sorted or rows
were inserted/deleted so all references must be relative to the
current row, so rewrite the formula as

D2: =MOD(OFFSET($D2,-1,0)+OFFSET($B2,-1,0)<>$B2,2)

or more likely that a change in column A or in Column B should force a
change in the Color Grouping.

D2: =MOD(OFFSET($D2,-1,0)+OR(OFFSET($A2,-1,0),OFFSET($B2,-1,0)<>$B2),2)

Then set up the Conditional Formatting
Select a cell on Row 1, then select the column you wish to color
such as A through C or use Ctrl+A to select all columns

Format, Conditional Formatting
condition 1, formulas is: =$D1=1

For more information on Conditional Formatting, and for a
pictorial review of this reply see
http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping

For more information on the worksheet formulas involved, see your HELP (F1)
MOD Worksheet Function
OFFSET Worksheet Function

For more information specifically on use of OFFSET as used here
http://www.mvps.org/dmcritchie/excel/offset.htm

 

[Guest]

Works perfectly. Thankyou very much.

What was wanted was a Irregular Color Banding, Color Grouping with Conditional Formatting

which in appearance is somewhat similar to Color Banding on Chip Pearson's site
http://www.cpearson.com/excel/banding.htm
except the areas will be dependent on the data and irregular number of rows.

If the column to be checked is B and the existing data is in A through C
then a helper column can be created in column D

D1: 0
D2: =MOD($D1+($B1<>$B2),2)

so that we will have 0 or 1, the rows with 1's will be colored by Conditional Formatting.

but that formula would not work if the data were sorted or rows
were inserted/deleted so all references must be relative to the
current row, so rewrite the formula as

D2: =MOD(OFFSET($D2,-1,0)+OFFSET($B2,-1,0)<>$B2,2)

or more likely that a change in column A or in Column B should force a
change in the Color Grouping.

D2: =MOD(OFFSET($D2,-1,0)+OR(OFFSET($A2,-1,0),OFFSET($B2,-1,0)<>$B2),2)

Then set up the Conditional Formatting
Select a cell on Row 1, then select the column you wish to color
such as A through C or use Ctrl+A to select all columns

Format, Conditional Formatting
condition 1, formulas is: =$D1=1

For more information on Conditional Formatting, and for a
pictorial review of this reply see
http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping

For more information on the worksheet formulas involved, see your HELP (F1)
MOD Worksheet Function
OFFSET Worksheet Function

For more information specifically on use of OFFSET as used here
http://www.mvps.org/dmcritchie/excel/offset.htm