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.

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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

"Govind" wrote:

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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


"Linc" wrote:


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

"Govind" wrote:

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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

"Michael" wrote:

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


"Linc" wrote:


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

"Govind" wrote:

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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

"Michael" wrote ...
=MOD(ROW(),2)=0

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).

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Linc" wrote in message ...

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

"Govind" wrote:

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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" wrote:

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).

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Linc" wrote in message ...

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

"Govind" wrote:

Hi,

Try using Format-Conditional formatting to achieve the same.

Govind.

Linc wrote:

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.

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.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Linc" wrote
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.

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


"Linc" wrote:


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.