Locking cell color pattern when sorting rows?

  • Thread starter Thread starter Sanjay Punjab
  • Start date Start date
S

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
 
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)
 
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
 
Alex@JPCS said:
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).
Click to expand...

<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.
 
Boys -
Sorry I mentioned the spelling thing.
Anybody have comments or improvements on "FastColour" ?

Alex@JPCS
 
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
 
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?
 
Back
Top