Locking cell color pattern when sorting rows?
Sanjay Punjab
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
Ken Wright
Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to 'formula is' and put
in =MOD(ROW(),2)=1
Now click on the format button and choose a nice pastel colour from the patterns tab. Hit OK till
you are out and you are done.
You could also put in
=MOD(ROW(),2)=0 if you wanted the other rows to be coloured.
You could also put in
=MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured.
You could also put in
=MOD(ROW(),4)=0 if you wanted every 4th row to be coloured.
You could also put in
=MOD(ROW(),5)=0 if you wanted every 5th row to be coloured.
But you probably get the drift by now. Flipping the 1 and the 0 will determine whether or not you
start with a coloured or a non-coloured row.
If you want to create alternate green bars made up of say 3 rows (or any other color), you can
also use:
For every three rows:
=MOD(ROW()-1,6)<3
For every four rows:
=MOD(ROW()-1,8)<4
For other number of shadings, just make the 2nd number (6 or 8 in example) twice as much as the
3rd number (3 or 4 in example)
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
"Sanjay Punjab" <picli...@yahoo.com> wrote in message
news:97d41df6.03080...@posting.google.com...
Mark Rosenkrantz
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 in...@rosenkrantz.nl
"Sanjay Punjab" <picli...@yahoo.com> wrote in message
news:97d41df6.03080...@posting.google.com...
David McRitchie
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
"Mark Rosenkrantz" <in...@rosenkrantz.nl> wrote in message news:bgl7uj$cc9$1...@reader11.wxs.nl...
Alex@JPCS
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
"David McRitchie" <dmcri...@msn.com> wrote in message
news:OBN0u7n...@TK2MSFTNGP11.phx.gbl...
Slartibartfast
news:cvuXa.957$_a4.2...@news20.bellglobal.com...
> 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.
--
Slartibartfast
To reply by email, remove the FJORDS from my address
Harlan Grove
..
>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.
First we expropriate your colonies, then your language . . . but we're not
greedy. You can keep your weather and your royal family.
Ken Wright
it away whilst it is like this anyway. :-)
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
"Harlan Grove" <hrl...@aol.com> wrote in message news:v0CXa.14337$cJ5....@www.newsranger.com...
Alex@JPCS
Sorry I mentioned the spelling thing.
Anybody have comments or improvements on "FastColour" ?
Alex@JPCS
"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:uLcFKXyW...@TK2MSFTNGP12.phx.gbl...
David McRitchie
In your news browser you should see 10 postings including this one
I believe the answer to question was in the first reply by Ken Wright
there were additional comments. You can see the entire thread at
http://google.com/groups?threadm=eGxdpXmW...@TK2MSFTNGP10.phx.gbl
The websites that would best fit your request is
Color Banding With Conditional Formatting
http://www.cpearson.com/excel/banding.htm
Excel User Tip: Alternate row shading using Conditional Formatting
http://j-walk.com/ss/excel/usertips/tip043.htm
Conditional Formatting was introduced in Excel 97. For earlier
versions you would need to run a macro.
Watch that your shading does not overwhelm the data on the line.
Few people would actually start with irrelevant comments, or
piggy-back questions had the original questions not been
answered. Is there some aspect that was not covered.
---
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
"Alex@JPCS" <apj....@sympatico.ca> wrote in message news:i%NXa.1765$_a4.3...@news20.bellglobal.com...
Alex@JPCS
Perhaps yo have missed my point. (I have the full thread, BTW).
In my first post in this thread, I posted a routine called FastColour as an
ALTERNATIVE to Conditional Formatting, since I have found that too much CF
can cause "Out of Memory" problems in a large app.
At the end of the thread, I was hoping that I might attract comment on, or
other subroutine techniques to, the FastColour routine.
Alex@JPCS
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#TE5i01W...@TK2MSFTNGP10.phx.gbl...
David McRitchie
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
---
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
"Alex@JPCS" <apj....@sympatico.ca> wrote in message news:MKPXa.1152$ZV6.1...@news20.bellglobal.com...
Ken Wright
--
Regards
Ken.......................
David McRitchie
http://google.com/groups?selm=OA%24oSOIl%24GA.248%40cppssbbsa03
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" <ken.w...@NOSPAMntlworld.com> wrote ...
Ken Wright
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?
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------
"David McRitchie" <dmcri...@msn.com> wrote in message
news:e1gkm54W...@TK2MSFTNGP12.phx.gbl...