Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
I would like to colour every second row. Like if I have the 100 first
rows in use then every second row should have a colour, and if I later on add or delete more rows, then every second row should still be coloured. I have manual coloured every second row, but if I delete a row then there will be three rows with the same colour beside each other. Is it possible to do it this way ?? -- Mit fotoalbum www.photo.activewebsite.dk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
use conditional formatting
1st condition =MOD(ROW(),2)=1 then choose your formats 2nd condition =MOD(ROW(),2)=0 then choose your formats i use both because i usually put boders around the cells and if you just use 1, the borders are around cells in every other row use 1 of the above if you just want to color the rows if you want code, this makes every other row in the specified range green with borders around the cells sub color_rows() dim lastrow as long lastRow = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row With Range("C4:C" & lastRow) .Interior.ColorIndex = 22 .Borders(xlLeft).LineStyle = xlContinuous .Borders(xlRight).LineStyle = xlContinuous .Borders(xlTop).LineStyle = xlContinuous .Borders(xlBottom).LineStyle = xlContinuous End With end sub -- Gary "SpookiePower" wrote in message . dk... I would like to colour every second row. Like if I have the 100 first rows in use then every second row should have a colour, and if I later on add or delete more rows, then every second row should still be coloured. I have manual coloured every second row, but if I delete a row then there will be three rows with the same colour beside each other. Is it possible to do it this way ?? -- Mit fotoalbum www.photo.activewebsite.dk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
Hi, Check out this link http://www.ozgrid.com/Excel/alternate-row-color.htm. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=503235 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
See http://www.xldynamic.com/source/xld.CF.html#rows for an example.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "SpookiePower" wrote in message . dk... I would like to colour every second row. Like if I have the 100 first rows in use then every second row should have a colour, and if I later on add or delete more rows, then every second row should still be coloured. I have manual coloured every second row, but if I delete a row then there will be three rows with the same colour beside each other. Is it possible to do it this way ?? -- Mit fotoalbum www.photo.activewebsite.dk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
"broro183" skrev i en meddelelse
... Hi, Check out this link http://www.ozgrid.com/Excel/alternate-row-color.htm. Thanks. But I can't get it to work. I did what is mention below, but all the rows are still white ?? Automatically Expand/Contract Alternate Row Colors/Color Banding The simple method shown above is fine for a static table, but it will apply the format to all odd row numbers that do not yet have data. For example, we use the range A1:D6 but could use A1:D100 so that as our table has more data added the new row of data will be color coded automatically, while all unused rows will remain blank. 1) Select the A1:D100, starting from A1. 2) Go to FormatConditional Formatting and choose "Formula is:" 3) In the box to the right, type the formula as shown below; =AND(MOD(ROW(),2),COUNTA($A1:$D1)) 4) Click the "Format" button and choose your desired cell shading under "Patterns". Then "Ok" and "Ok" again. Now only the used range of A1:D100 will have the alternate row color/banding. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
I cant make the most simple one of them to work.
Either all the rows are white or I get this error message - http://www.activewebsite.dk/excel.jpg and then it point at ,2 in the line =MOD(ROW(),2)=0 I'm using excel 2003 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
Hi, try again... SpookiePower Wrote: I cant make the most simple one of them to work. Either all the rows are white or I get this error message - http://www.activewebsite.dk/excel.jpg and then it point at ,2 in the line =MOD(ROW(),2)=0 I'm using excel 2003 Have another look at the example, all that needs to be entered is "=MOD(ROW(),2)", your picture shows that you have typed "=MOD(ROW(),2)=0". The "=0" is not needed. hth Rob Brockett NZ always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=503235 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
Maybe you need a formula of
=REST(RĘKKE();2)=0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SpookiePower" wrote in message . dk... I cant make the most simple one of them to work. Either all the rows are white or I get this error message - http://www.activewebsite.dk/excel.jpg and then it point at ,2 in the line =MOD(ROW(),2)=0 I'm using excel 2003 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
"Bob Phillips" skrev i en meddelelse ...
Maybe you need a formula of =REST(RĘKKE();2)=0 I'm using the english version not the danish. But I'll try it. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
I found the error.
In all the examples I have found they use a "," before 2 - =MOD(ROW(),2) It works when I use ";" insted - =MOD(ROW();2) But why do the examples use "," http://www.xldynamic.com/source/xld.CF.html#top http://www.ozgrid.com/Excel/alternate-row-color.htm |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
SpookiePower skrev: I found the error. In all the examples I have found they use a "," before 2 - =MOD(ROW(),2) It works when I use ";" insted - =MOD(ROW();2) But why do the examples use "," http://www.xldynamic.com/source/xld.CF.html#top http://www.ozgrid.com/Excel/alternate-row-color.htm Using "," or ";" is dependent on localization IIRC, thus an 'english Excel install' uses "," but a 'swedish Excel install' uses ";". Erm, or vice versa :) /impslayer, aka Birger Johansson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
Because we are using English language setting, where comma is not used as a
decimal separator in numbers, so it can be used as the function separator. As Continental settings us a comma in numbers, a different character is used as a function separator. If you noticed in my Danish version I included a ;. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "SpookiePower" wrote in message . dk... I found the error. In all the examples I have found they use a "," before 2 - =MOD(ROW(),2) It works when I use ";" insted - =MOD(ROW();2) But why do the examples use "," http://www.xldynamic.com/source/xld.CF.html#top http://www.ozgrid.com/Excel/alternate-row-color.htm |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every second row ?
I have written a small macro using CurrentRegion where in VBA detects last column and last row in used area and only shades that range. I also color header row differently. I strongly recommend that you store this procedure or your own procedure in VBA project module and create a custom button in a toolbar associated with this procedure. All that you have to do is select any cell in the table and click this button it. The procedure I wrote and use is ( I call it EasyRead named after continuous stationery used for computer printouts ) Sub EasyRead() Dim MyArea As Object Set MyArea = ActiveCell.CurrentRegion ColNum = MyArea.Columns.Count RowNum = MyArea.Rows.Count FirstCol = MyArea.Column FirstRow = MyArea.Row OddFlag = True With Range(Cells(FirstRow, FirstCol), Cells(FirstRow, FirstCol + ColNum - 1)) ..Interior.ColorIndex = 40 ..Font.Bold = True End With For i = 1 To RowNum - 1 Set NewRow = Range(Cells(FirstRow + i, FirstCol), Cells(FirstRow + i, FirstCol + ColNum - 1)) If OddFlag = True Then NewRow.Interior.ColorIndex = 2 Else NewRow.Interior.ColorIndex = 20 End If OddFlag = Not (OddFlag) Next i With MyArea.Borders ..LineStyle = xlContinuous ..Weight = xlThin ..ColorIndex = xlAutomatic End With End Sub -- avveerkar ------------------------------------------------------------------------ avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338 View this thread: http://www.excelforum.com/showthread...hreadid=503235 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Powerpoint / Excel: custom pp RGB color doesn't match identical Excelcustom RGB color | Charts and Charting in Excel | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) | |||
Browse Forms Controls and change TextBox color based on cell color | Excel Programming |