Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
I would like to have my spreadsheets to have rows that have a color/grey
alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
MGM
The easiest way of achieving this is to use the conditional formating facility. Firstly select all the cells that you want the formating to apply too. Then on the worksheet menu bar select Format, Conditional Format. In the left hand dropdown box choose 'Formula Is' rather than the default 'Cell Value Is'. In the edit box that then appears enter: =IF(MOD(ROW(),2)=1,TRUE,FALSE) Then click on the 'Format' button and set the Pattern style to grey (or whatever other colour you fancy). If you want a wider spacing between highlighted lines simply change the number in the formula above, for example: =IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row. This is much easier than using VB. Hope it works for you! Peter Morris "mgm" wrote in message ... I would like to have my spreadsheets to have rows that have a color/grey alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
You don't need all of the formula that Peter provides
=MOD(ROW(),2)=1 will return either TRUE or FALSE that CF will respond to. In general, the formula is =MOD(ROW(),m)=n where m = number of rows in the group, and n is the first striped row. n mustn't equal or exceed m. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Peter M" wrote in message ... MGM The easiest way of achieving this is to use the conditional formating facility. Firstly select all the cells that you want the formating to apply too. Then on the worksheet menu bar select Format, Conditional Format. In the left hand dropdown box choose 'Formula Is' rather than the default 'Cell Value Is'. In the edit box that then appears enter: =IF(MOD(ROW(),2)=1,TRUE,FALSE) Then click on the 'Format' button and set the Pattern style to grey (or whatever other colour you fancy). If you want a wider spacing between highlighted lines simply change the number in the formula above, for example: =IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row. This is much easier than using VB. Hope it works for you! Peter Morris "mgm" wrote in message ... I would like to have my spreadsheets to have rows that have a color/grey alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
Hi Peter
this can be shortened to =MOD(ROW(),2)=1 see also http://www.cpearson.com/excel/banding.htm for more general formulas Frank Peter M wrote: MGM The easiest way of achieving this is to use the conditional formating facility. Firstly select all the cells that you want the formating to apply too. Then on the worksheet menu bar select Format, Conditional Format. In the left hand dropdown box choose 'Formula Is' rather than the default 'Cell Value Is'. In the edit box that then appears enter: =IF(MOD(ROW(),2)=1,TRUE,FALSE) Then click on the 'Format' button and set the Pattern style to grey (or whatever other colour you fancy). If you want a wider spacing between highlighted lines simply change the number in the formula above, for example: =IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row. This is much easier than using VB. Hope it works for you! Peter Morris "mgm" wrote in message ... I would like to have my spreadsheets to have rows that have a color/grey alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
Bob, Frank,
Many thanks - I'll delete my 'the easiest way' and replace with 'nearly the easiest way'! I didn't realise that that would work - clearly a philosophy to employ elsewhere. Thanks. Peter "Frank Kabel" wrote in message ... Hi Peter this can be shortened to =MOD(ROW(),2)=1 see also http://www.cpearson.com/excel/banding.htm for more general formulas Frank Peter M wrote: MGM The easiest way of achieving this is to use the conditional formating facility. Firstly select all the cells that you want the formating to apply too. Then on the worksheet menu bar select Format, Conditional Format. In the left hand dropdown box choose 'Formula Is' rather than the default 'Cell Value Is'. In the edit box that then appears enter: =IF(MOD(ROW(),2)=1,TRUE,FALSE) Then click on the 'Format' button and set the Pattern style to grey (or whatever other colour you fancy). If you want a wider spacing between highlighted lines simply change the number in the formula above, for example: =IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row. This is much easier than using VB. Hope it works for you! Peter Morris "mgm" wrote in message ... I would like to have my spreadsheets to have rows that have a color/grey alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or vb to alternating row color
Thanks guys.... just what I needed!
"Peter M" wrote in message ... Bob, Frank, Many thanks - I'll delete my 'the easiest way' and replace with 'nearly the easiest way'! I didn't realise that that would work - clearly a philosophy to employ elsewhere. Thanks. Peter "Frank Kabel" wrote in message ... Hi Peter this can be shortened to =MOD(ROW(),2)=1 see also http://www.cpearson.com/excel/banding.htm for more general formulas Frank Peter M wrote: MGM The easiest way of achieving this is to use the conditional formating facility. Firstly select all the cells that you want the formating to apply too. Then on the worksheet menu bar select Format, Conditional Format. In the left hand dropdown box choose 'Formula Is' rather than the default 'Cell Value Is'. In the edit box that then appears enter: =IF(MOD(ROW(),2)=1,TRUE,FALSE) Then click on the 'Format' button and set the Pattern style to grey (or whatever other colour you fancy). If you want a wider spacing between highlighted lines simply change the number in the formula above, for example: =IF(MOD(ROW(),5)=1,TRUE,FALSE) highlights every 5th row. This is much easier than using VB. Hope it works for you! Peter Morris "mgm" wrote in message ... I would like to have my spreadsheets to have rows that have a color/grey alternate to make reading the data clearer and easier to follow. Is there a way to write a macro or vba that could be triggered and accessed by all my workbooks? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Alternating Rows with a Color | Excel Worksheet Functions | |||
Alternating Color Rows | Excel Discussion (Misc queries) | |||
Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
How do I set an auto alternating row color? | Excel Discussion (Misc queries) | |||
Filling alternating rows in a worksheet with color backgroud | Excel Programming |