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...
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...
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...
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...
<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
First we expropriate your colonies, then your language . . . but we're not
greedy. You can keep your weather and your royal family.
--
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
"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:uLcFKXyW...@TK2MSFTNGP12.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...
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...
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...
--
Regards
Ken.......................
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 ...
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...