Coloring Alternate lines

Prev: How to Show/Hide Columns or Rows with no data via VB
Next: Change Times

From: Jim Berglund on 31 May 2010 02:20

---

I want to color columns A to E on every other line.
I can't find an ISEVEN or ISINTEGER function, so how do I do That in VB?

Thanks,
Jim Berglund

From: Jim Berglund on 31 May 2010 02:32

---

I'm getting a Next without For error in the following code, and can't figure
out why...
BTW, If I get this fixed, will this code work for coloring alternate lines?
Jim


Sub SortAndColor()
Dim q, i As Long


With ActiveSheet
.Rows("1:1").Delete Shift:=xlUp
q = .Range("A" & Rows.Count).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("E1:E" & q),
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C1:C" & q),
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort
.SetRange Range("A1:E" & q)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply


For i = 1 To q
If Int(i / 2) Then
.Range(Cells(i, 1), Cells(i, 5)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0
End With
Next

.PageSetup.PrintArea = "$A:$E"

End With

End Sub

"Jim Berglund" <jazzzbo(a)shaw.ca> wrote in message
news:O98mxlIALHA.4388(a)TK2MSFTNGP04.phx.gbl...
> I want to color columns A to E on every other line.
> I can't find an ISEVEN or ISINTEGER function, so how do I do That in VB?
> Thanks,
> Jim Berglund

From: sali on 31 May 2010 05:40

---

"Jim Berglund" <jazzzbo(a)shaw.ca> je napisao u poruci interesnoj
grupi:u16INsIALHA.4652(a)TK2MSFTNGP06.phx.gbl...
> BTW, If I get this fixed, will this code work for coloring alternate
> lines?
> For i = 1 To q

> .Range(Cells(i, 1), Cells(i, 5)).Select
> With Selection.Interior

for i=1 to q step 2
will affect every-second-row [even or odd, depending on starting used]

you don't need range-select-with-selection, you may shortly:
with .Range(Cells(i, 1), Cells(i, 5)).interior

From: Jim Berglund on 31 May 2010 09:50

---

Thanks very much. I didn't even think of that!
Jim

"sali" <sali(a)euroherc.hr> wrote in message
news:evITLSKALHA.5848(a)TK2MSFTNGP06.phx.gbl...
> "Jim Berglund" <jazzzbo(a)shaw.ca> je napisao u poruci interesnoj
> grupi:u16INsIALHA.4652(a)TK2MSFTNGP06.phx.gbl...
>> BTW, If I get this fixed, will this code work for coloring alternate
>> lines?
>> For i = 1 To q
>
>> .Range(Cells(i, 1), Cells(i, 5)).Select
>> With Selection.Interior
>
> for i=1 to q step 2
> will affect every-second-row [even or odd, depending on starting used]
>
> you don't need range-select-with-selection, you may shortly:
> with .Range(Cells(i, 1), Cells(i, 5)).interior
>
>
>

 | 
Pages: 1
Prev: How to Show/Hide Columns or Rows with no data via VB
Next: Change Times