selective sheet formating

[Guest]

On a sheet I have a list of names with information beside it. The name may
repeat several times one after the other in the list and the list could be
anywhere from 1 to 140 rows. So it is easier to read I was wondering whether
it is possible to change the cell shading (for part of the row) based on the
name automatically?

The names would be unknown to the macro:

Name1 <yellow shading>
Name1 <yellow shading>
Name1 <yellow shading>
Name2 <the name changed, so pick another colour, blue>
Name3 <the name changed, so pick another colour, green>
Name3 <green shading>
<blank> <leave shading>

I am pretty sure I could do all the coding, simply by reading each cell in
turn, checking the format and making changes as required ( or is there a
smarter way?) and run from say a button on the page, BUT can this macro be
started simply because someone changed the name in a cell?

 

[David McRitchie]

Hi Trefor,
see Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping

BTW the solution is for two colors which is all you need,
but it will not handle blank rows. If you want to process blank rows
differently the way you actually marked you would need a macro. If you can
use Conditional Formatting it is a lot simpler and faster.

 

[Guest]

David,

Many thanks. AND if you don't mind me making a suggestion in return:

=IF($E2="",2,MOD(OFFSET($E2,-1,0)+ OR($A2<>OFFSET($A2,-1,0),
$B2<>OFFSET($B2,-1,0)),2))

The fix for an empty cell AND the possible use of three colours in the
Conditional Formatting.

Thanks again.

 

[David McRitchie]

Hi Trefor,
If this applies to the example on my web page,
http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping
then your formula would be put into E2 (anywhere but column A or B
and would be written as (the $E2 is changed to $A2 as below AND avoid a circular reference)
E2: =IF($A2="",2,MOD(OFFSET($E2,-1,0)+ OR($A2<>OFFSET($A2,-1,0),
$B2<>OFFSET($B2,-1,0)),2))

The color after the blank row(s) will always be the color you assign to 1
So the color after may match the color before the blank line
or if may not match. If you left the formula alone you would
have a consistent color banding with the blank row treated
as a normal change. The Effect to me with your change is that by
adding a 3rd coloring or a blank row is that it is breaking apart equals when
the colors above and below match.

So I would suggest:
E2:
= IF($A2="",IF($A1="",$E1,$E1+2),MOD(OFFSET($E2,-1,0)
+ OR($A2<>OFFSET($A2,-1,0), $B2<>OFFSET($B2,-1,0)),2))

C.F. $E1=1 yellow
C.F. $E1>1 turquiose (value 2 or 3)
anything else is default without color

with the caveat that an empty cell in column A will alway cause an unequal
break if the row above and below are equal to each other but not empty.

My web page has been modified to incorporate your suggestion, but
without showing use of a blank row (col A) in the example..