Locking cell color pattern when sorting rows?

[Sanjay Punjab]

I am creating a spreadsheet where I want every alternating row to have
a light grey solid pattern that acts as shading, so it is easier to
follow individual rows from column to column on a printed page.
The problem is that when I choose to sort rows by a specific data
property in any column, the shading pattern becomes messed up, because
there doesnt seem to be a way to lock the shaded pattern of each cell.
Is there a solution to this problem? Thanks

 

[Mark Rosenkrantz]

Sanjay;

Might this be a solution ?
Seperate lines between groups of data by using conditional formatting.

- In a spreadsheet, build a table.
( B4..C22 where B4 is type and C4 is value )

First sort Range (B5..C22) and then select it.
Choose: Format |Conditional Formatting from the menu.
In the first Dropdownbox choose : Fomula =
In the Conditionbox type : =$B5<>$B6

Set the condition by clicking Format. In this case choose a line at the
bottom.

When you look at the table, a line appears after every new type.

!! Typing the reference to B5, does not mean that all conditional
formatting refers to B5.
The Conditional formatting in cell B6, does refers to B6 and so on.

This is where you can find the tip !

http://www.rosenkrantz.nl/functiontip07.htm

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

 

[Alex@JPCS]

For those who are in danger of running out of room with too many conditional
formats (That would be me), this works well enough: (P.S. Please excuse the
Canadian spelling of "Color")

This routine will identify every second visible row, collect it in a union
of ranges, and finally set the colour in a single step. Both white and
non-white backgrounds need to be processed to clear previous colors (one
could also set all colours in the range white, and then just set the
non-white colour).

Alex@JPCS



Sub FastColour()
Dim Sht As Worksheet
Dim Target as Range
Dim rwrange(0 To 1) As Range 'Array of ranges
Dim visrow As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False

Set Target = Sht.Range("DataRange") 'Identifies range of
data to be coloured
tgtrows = Target.Rows.Count

n = 0
On Error GoTo ERR01 'First Union
Command will cause an error
With Target
For rw = 1 To tgtrows
Set Tgt2 = .Rows(rw)
'Assign for yellow background
If Not Tgt2.Hidden Then 'Ensures
after a filter command that only visible rows are coloured
n = n + 1
visrow = n Mod 2
Set rwrange(visrow) = Union(rwrange(visrow),
Tgt2)
End If
Next rw
On Error GoTo 0

End With

If rwrange(0) Is Nothing Then
GoTo RW1
Else
rwrange(0).Interior.ColorIndex = vbWhite
End If
RW1:
If rwrange(1) Is Nothing Then
GoTo XIT
Else
rwrange(1).Interior.ColorIndex = vbRed 'Pick a
color ........
End If

XIT:
Set Sht = Nothing
Set Target = Nothing
Set Tgt2 = Nothing
For i = LBound(rwrange) To UBound(rwrange)
Set rwrange(i) = Nothing
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

ERR01:
Err.Clear
Set rwrange(visrow) = Tgt2
Resume Next

End Sub

The question relates to simulating the green bar
computer paper with alternating color stripes of generally
several lines high for each strip when on paper, or 1 line high
for spreadsheets. But Mark's answer on grouping on the
data was interesting.

But first, adding to Ken's examples, there are a few more things that
can be picked up in Chip Pearson's page, if you want to make
additional adjustments.
Color Banding With Conditional Formatting
http://www.cpearson.com/excel/banding.htm

---
Back to Mark's example involving grouping on the data (which has
nothing to do with the question). The sorting mentioned also
has nothing to do with the Conditional Formatting. There is
one thing I stress on my condfmt.htm page and that is that the
formula is *based* on the active cell. You will see that in
the example he provided but it is not very obvious. Directions
below are for the entire sheet.

First use Ctrl+A to select all cells then apply border
formatting (inside and outline) of dotted lines -- do this because
C.F. will wipe out gridlines.

Then Select all cells, Ctrl+A, with A1 being the active cell,
Formula 1 is: =$A1<>$A2
borders choose dotted lines, then left and right borders
choose solid line, then bottom border

I had not realized before that C.F. did not have a full complement
of borders so one can't choose a thicker line, so the default of
dotted lines helps to make the solid lines stand out.
(just in case Excel 2002 differs, mine is Excel 2000)
---
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

Sanjay;

Might this be a solution ?
Seperate lines between groups of data by using conditional formatting.

- In a spreadsheet, build a table.
( B4..C22 where B4 is type and C4 is value )

First sort Range (B5..C22) and then select it.
Choose: Format |Conditional Formatting from the menu.
In the first Dropdownbox choose : Fomula =
In the Conditionbox type : =$B5<>$B6

Set the condition by clicking Format. In this case choose a line at the
bottom.

When you look at the table, a line appears after every new type.

!! Typing the reference to B5, does not mean that all conditional
formatting refers to B5.
The Conditional formatting in cell B6, does refers to B6 and so on.

This is where you can find the tip !

http://www.rosenkrantz.nl/functiontip07.htm

Mark Rosenkrantz.

More Excel ? www.rosenkrantz.nl or (e-mail address removed)

 

[Slartibartfast]

For those who are in danger of running out of room with too many conditional
formats (That would be me), this works well enough: (P.S. Please excuse the
Canadian spelling of "Color")

This routine will identify every second visible row, collect it in a union
of ranges, and finally set the colour in a single step. Both white and
non-white backgrounds need to be processed to clear previous colors (one
could also set all colours in the range white, and then just set the
non-white colour).

<SNIP>

As a Brit I can tell you there is absolutely no reason for you to apologise
for spelling it "Colour". This is in fact the correct spelling.

 

[Alex@JPCS]

Boys -
Sorry I mentioned the spelling thing.
Anybody have comments or improvements on "FastColour" ?

Alex@JPCS

 

[David McRitchie]

Here are comments then:

You did not get comments because you did not ask for comments.
Heck we don't even get answers telling anyone which solution works when
there is more than one suggestion.

Your posting really had very little to do with the question (in my opinion),
so is not helping the poster
So I would assume that it does not present the best solution to the
original poster and they would not use it.
Probably provides an additional
burden in trying to figure out best answer, how to procede..
If you have something new post it to one group and **ask for comments**
and what in the way of comments you are especially looking for.
Since it is code it should be to the programming group - ONLY.
Too late for that you get your your comments here.

Here are some comments on the macro:
-- If you can do something with conditional formatting using builtin
worksheet functions, then no macro is going to match efficiency.
-- If C.F. is not suitable then normally one switches to using a
Change Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#case
-- Every time you insert or delete a row, or do a sort you have to
rerun the macro. No comparison to ease of use of C.F.
-- The technique might help those that have sets of rows
for an item and want to conditionally format only say each third row
beginning at the 4th row (first row having descriptions) and would have
difficulty with the limits of C.F. (but that is questionable)
-- You did not DIM all of your variables, anything to do with row numbers
should be LONG and not Integer. (plan for future same for columns)
you have about 5-7 variable that need defining/redefinition.
Would have listed them but could not break into Excel.
-- Tying up computer for ten minutes at 100% CPU is not conducive
to a working solution. Had to kill Excel. So definitely have to
change my calculation back to automatic, and more serious things
I'd rather not mention.
-- Requiring a defined range to go with the macro is an extra burden.

Also the orignal poster should have posted to only one newsgroup
microsoft.public.excel.worksheet.functions would have been fine,
and if you're not sure then one would use microsoft.public.excel.misc

 

[Ken Wright]

Dave, what are you saying - You mean you're *not* a mindreader????????????? <g>

 

[David McRitchie]

Not even Tom Ogilvy can read minds.
http://google.com/groups?selm=OA$oSOIl$GA.248@cppssbbsa03

BTW, I don't recall memory problems with Conditional Formatting,
almost sounds like applying C.F. to cells individually rather than in big
selections.

 

[Ken Wright]

LOL - Well I'll be........

As for the CF - Just tried it in a single cell, and then copied that cells formats to every cell
on the sheet. No probs and only a 37K file. Would that count as applying to cells singly, as
opposed to selecting the range and then applying the CF one time?