Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
Hi, I am a newbie when it comes to using excel. I know nothing about codes or
formatting, or even the right terminology for each box/section/row (I hope i chose the right discussion group for this) Right now, I am using excel in a very simple way. I have a list of columns across the top that go from A to P, each with a different heading. Then down on the left, I am up to line 190. I am not doing any calculations, only text like names, ph #'s, email address, contact info, notes, dates, etc. 1) how far down do you go before switching to sheet 2 or 3? 2) how much info is too much for one excel sheet? 3)how do i either: a) color code the entire row (eg. line 5, A - P) without seperately configuring each column (A - P) with it's own color? [I've been right clicking on a box (eg. A5), clicking "format cells", then "patterns", then choosing a color, then copying and pasting it all the way down in the boxes i want it, and repeating these steps for columns B - P] b) color code alternate lines for easy visibility? [note: i found out how to highlight every 2nd row, but this does not help me, as some sections are 2 rows down, some are 3, 4, etc.] eg A-P lines 18-19 is one grouping, whereas A-P lines 21-25 is another. Sorry, I hope this makes sense to someone. I tried to be as exact as possible. D |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
I'd be guessing about questions 1 & 2
Conditional formatting will take care of your alternate line shading. Select all cells on your spreadsheet (click the small grey box above the row numbering and to the left of column lettering) 1) Click Format 2) Click Conditional Formatting 3) Change the drop-down box on the left to 'Fromula Is' 4) In the text box next to it enter '=MOD(ROW(),2)=1' (without the quotes) 5) Click the [format] button 6) Choose a format 7) Click OK Start experimenting with different border and pattern combinations until you find something you like. Alternatively, if alternate line shading is something you plan to do often, look for Arron Blood's ledger shade add-in on this link: http://www.xl-logic.com/pages/addins.html "darkdred" wrote in message ... Hi, I am a newbie when it comes to using excel. I know nothing about codes or formatting, or even the right terminology for each box/section/row (I hope i chose the right discussion group for this) Right now, I am using excel in a very simple way. I have a list of columns across the top that go from A to P, each with a different heading. Then down on the left, I am up to line 190. I am not doing any calculations, only text like names, ph #'s, email address, contact info, notes, dates, etc. 1) how far down do you go before switching to sheet 2 or 3? 2) how much info is too much for one excel sheet? 3)how do i either: a) color code the entire row (eg. line 5, A - P) without seperately configuring each column (A - P) with it's own color? [I've been right clicking on a box (eg. A5), clicking "format cells", then "patterns", then choosing a color, then copying and pasting it all the way down in the boxes i want it, and repeating these steps for columns B - P] b) color code alternate lines for easy visibility? [note: i found out how to highlight every 2nd row, but this does not help me, as some sections are 2 rows down, some are 3, 4, etc.] eg A-P lines 18-19 is one grouping, whereas A-P lines 21-25 is another. Sorry, I hope this makes sense to someone. I tried to be as exact as possible. D |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
Hi, hope this gets you started:
1) & 2) There are 65,536 rows and 256 columns in an excel worksheet. 3) Look at these links for some help with selecting and coloring the rows: http://www.contextures.com/xlCondFormat01.html http://www.cpearson.com/excel/cformatting.htm Regards--Lonnie M. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
WOW! That's alot of rows and columns. So I shouldn't change my sheets? :-)
I'll have to check out those links sometime. Thanks. D "Lonnie M." wrote: Hi, hope this gets you started: 1) & 2) There are 65,536 rows and 256 columns in an excel worksheet. 3) Look at these links for some help with selecting and coloring the rows: http://www.contextures.com/xlCondFormat01.html http://www.cpearson.com/excel/cformatting.htm Regards--Lonnie M. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
Just to clarify: if I enter that formula, how will the shading appear?
Different on every 2nd row? By the way guys, thanks for all your help. While I was waiting, I did some experimenting, and I learned alot about how to do color shading, deleting of rows and columns, etc., so for now, i'm good, but i'll try to check out the links you gave. Thanks. D "Steve" wrote: I'd be guessing about questions 1 & 2 Conditional formatting will take care of your alternate line shading. Select all cells on your spreadsheet (click the small grey box above the row numbering and to the left of column lettering) 1) Click Format 2) Click Conditional Formatting 3) Change the drop-down box on the left to 'Fromula Is' 4) In the text box next to it enter '=MOD(ROW(),2)=1' (without the quotes) 5) Click the [format] button 6) Choose a format 7) Click OK Start experimenting with different border and pattern combinations until you find something you like. Alternatively, if alternate line shading is something you plan to do often, look for Arron Blood's ledger shade add-in on this link: http://www.xl-logic.com/pages/addins.html "darkdred" wrote in message ... Hi, I am a newbie when it comes to using excel. I know nothing about codes or formatting, or even the right terminology for each box/section/row (I hope i chose the right discussion group for this) Right now, I am using excel in a very simple way. I have a list of columns across the top that go from A to P, each with a different heading. Then down on the left, I am up to line 190. I am not doing any calculations, only text like names, ph #'s, email address, contact info, notes, dates, etc. 1) how far down do you go before switching to sheet 2 or 3? 2) how much info is too much for one excel sheet? 3)how do i either: a) color code the entire row (eg. line 5, A - P) without seperately configuring each column (A - P) with it's own color? [I've been right clicking on a box (eg. A5), clicking "format cells", then "patterns", then choosing a color, then copying and pasting it all the way down in the boxes i want it, and repeating these steps for columns B - P] b) color code alternate lines for easy visibility? [note: i found out how to highlight every 2nd row, but this does not help me, as some sections are 2 rows down, some are 3, 4, etc.] eg A-P lines 18-19 is one grouping, whereas A-P lines 21-25 is another. Sorry, I hope this makes sense to someone. I tried to be as exact as possible. D |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
this will format every other row in blue and put grid lines around each cell
from column A thru D for the number of rows on your sheet Option Explicit Dim lastrow As Long Dim rng As Range Sub Format_rows() lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row ' Conditional Formatting Set rng = Range("A1:D" & lastrow) With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)=1" End With With rng.FormatConditions(1).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With rng.FormatConditions(1).Interior.ColorIndex = 37 rng.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=MOD(ROW(),2)=0" With rng.FormatConditions(2).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(2).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(2).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(2).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With rng.FormatConditions(2).Interior.ColorIndex = 2 End Sub -- Gary "darkdred" wrote in message ... Hi, I am a newbie when it comes to using excel. I know nothing about codes or formatting, or even the right terminology for each box/section/row (I hope i chose the right discussion group for this) Right now, I am using excel in a very simple way. I have a list of columns across the top that go from A to P, each with a different heading. Then down on the left, I am up to line 190. I am not doing any calculations, only text like names, ph #'s, email address, contact info, notes, dates, etc. 1) how far down do you go before switching to sheet 2 or 3? 2) how much info is too much for one excel sheet? 3)how do i either: a) color code the entire row (eg. line 5, A - P) without seperately configuring each column (A - P) with it's own color? [I've been right clicking on a box (eg. A5), clicking "format cells", then "patterns", then choosing a color, then copying and pasting it all the way down in the boxes i want it, and repeating these steps for columns B - P] b) color code alternate lines for easy visibility? [note: i found out how to highlight every 2nd row, but this does not help me, as some sections are 2 rows down, some are 3, 4, etc.] eg A-P lines 18-19 is one grouping, whereas A-P lines 21-25 is another. Sorry, I hope this makes sense to someone. I tried to be as exact as possible. D |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel help, color coding rows
Yes, it colours every second row but, the best way to work it all out is to
experiment. "darkdred" wrote in message ... Just to clarify: if I enter that formula, how will the shading appear? Different on every 2nd row? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color coding dates in excel | New Users to Excel | |||
color coding | Excel Discussion (Misc queries) | |||
color coding | Excel Worksheet Functions | |||
Color coding the excel sheets | Excel Discussion (Misc queries) | |||
Color coding cells in Excel | Excel Discussion (Misc queries) |