excel help, color coding rows

[Guest]

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

 

[Steve]

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

 

[Lonnie M.]

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.

 

[Guest]

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

 

[Guest]

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

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

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

 

[Gary Keramidas]

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" & 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

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

 

[Steve]

Yes, it colours every second row but, the best way to work it all out is to
experiment.