conditional formatting or vba?
 

have a list of 200 accounts indentified with unique account numbers. each of
these accounts may have from one to x entries that would begin with account
number in column 2. these rows are sorted in ascending order

i'd like to use one green row and a default white row such that all of the
same account number entries would bee the same color and the next set of
account number would be the alternate color.
eg:
Line # Acct# Date Result
1 1 .... ....
2 1
3 2
4 3
Lines 1,2 and 4 would be green and line 3 would be white and so forth.

any suggestions

VBA.

Sub ColourRows()
Dim sVal
Dim i As Long
Dim nCol As Long

sVal = Range("A1").Value
nCol = 35
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
With Cells(i, "A")
If .Value < sVal Then
If nCol = 35 Then
nCol = xlColorIndexNone
sVal = .Value
Else
nCol = 35
End If
End If
.EntireRow.Interior.ColorIndex = nCol
End With
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"reno" wrote in message
...
have a list of 200 accounts indentified with unique account numbers. each
of
these accounts may have from one to x entries that would begin with
account
number in column 2. these rows are sorted in ascending order

i'd like to use one green row and a default white row such that all of the
same account number entries would bee the same color and the next set of
account number would be the alternate color.
eg:
Line # Acct# Date Result
1 1 .... ....
2 1
3 2
4 3
Lines 1,2 and 4 would be green and line 3 would be white and so forth.

any suggestions

"reno" wrote:
Line # Acct# Date Result
1 1 .... ....
2 1
3 2
4 3
Lines 1,2 and 4 would be green and line 3 would be white and so forth.

I'd suggest conditional formatting. Select all your cells, go to
Format-Conditional Formatting.
Then as your first condition, select "Formula Is", and in the formula
box (assuming account number is in column B), type:
=Mod($B2,2)

Then select the format you want.

If the "white" line formatting is different than the default, you can
add a second condition, using the formula:
=Not(Mod($B2,2))

The only real disadvantage (that I can see) is that you may not have
true alternating colours if you sort on something other than account #.

"." wrote in message
ups.com...
I'd suggest conditional formatting. Select all your cells, go to
Format-Conditional Formatting.
Then as your first condition, select "Formula Is", and in the formula
box (assuming account number is in column B), type:
=Mod($B2,2)

Then select the format you want.

If the "white" line formatting is different than the default, you can
add a second condition, using the formula:
=Not(Mod($B2,2))

The only real disadvantage (that I can see) is that you may not have
true alternating colours if you sort on something other than account #.

I may be wrong, but I doubt that the account numbers are really 1,2,3,4, I
think they were just simplified examples.

Bob Philips wrote:

I may be wrong, but I doubt that the account numbers are really 1,2,3,4, I
think they were just simplified examples.

That doesn't matter so long as they are sequential. If they aren't
sequential (perhaps because of closed accounts, etc), then this trick
wouldn't work, that's true.

I did not make myelf clear. here is a sample of the actual data

LN SID# Bnr Date D FG OH 1 MFS 07/13/04 Tue
2 1 MFS 07/17/04 Sat
3 1 MFS 09/02/04 Thu 17
4 1 MFS 09/16/04 Thu 15
5 1 MFS 10/26/04 Tue 14
6 1 MFS 12/16/04 Thu 12
7 1 MFS 01/18/05 Tue 12
8 1 MFS 03/10/05 Thu Y 15
9 1 MFS 04/28/05 Thu Y 11
10 1 MFS 04/28/05 Thu Y 11
11 1 MFS 07/19/05 Tue 10
12 3 MFS 07/12/05 Tue 7
13 4 MFS 07/13/04 Tue
14 4 MFS 09/03/04 Fri 10
15 4 MFS 11/09/04 Tue 4
16 4 MFS 11/09/04 Tue 4
17 4 MFS 01/05/05 Wed 12
18 4 MFS 03/09/05 Wed Y 7
19 4 MFS 06/07/05 7 Y 4
20 5 MFS 07/16/04 Fri
What I want to do is group by alternating colors based on the account
number. The SIDS are unique and not necessarily consecutive, in this example
there is no acct# 2. In the above example all SID 1 is green, the next SID,3
in this case would be green and forth.
Conditional is problematic, since the SIDs are not consecutive, this would
give the effect of odd/even. In this case SIDs both SIDs 1& 3 above would be
green using the MOD argument.

Is this a little more clear?
Thanks

Exactly what I said.

A small tweak to my code to start at line 2 works fine

Sub ColourRows()
Dim sVal
Dim i As Long
Dim nCol As Long

sVal = Range("A2").Value
nCol = 35
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
With Cells(i, "A")
If .Value < sVal Then
If nCol = 35 Then
nCol = xlColorIndexNone
sVal = .Value
Else
nCol = 35
End If
End If
.EntireRow.Interior.ColorIndex = nCol
End With
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"reno" wrote in message
...
I did not make myelf clear. here is a sample of the actual data

LN SID# Bnr Date D FG OH 1 MFS 07/13/04 Tue
2 1 MFS 07/17/04 Sat
3 1 MFS 09/02/04 Thu 17
4 1 MFS 09/16/04 Thu 15
5 1 MFS 10/26/04 Tue 14
6 1 MFS 12/16/04 Thu 12
7 1 MFS 01/18/05 Tue 12
8 1 MFS 03/10/05 Thu Y 15
9 1 MFS 04/28/05 Thu Y 11
10 1 MFS 04/28/05 Thu Y 11
11 1 MFS 07/19/05 Tue 10
12 3 MFS 07/12/05 Tue 7
13 4 MFS 07/13/04 Tue
14 4 MFS 09/03/04 Fri 10
15 4 MFS 11/09/04 Tue 4
16 4 MFS 11/09/04 Tue 4
17 4 MFS 01/05/05 Wed 12
18 4 MFS 03/09/05 Wed Y 7
19 4 MFS 06/07/05 7 Y 4
20 5 MFS 07/16/04 Fri
What I want to do is group by alternating colors based on the account
number. The SIDS are unique and not necessarily consecutive, in this
example
there is no acct# 2. In the above example all SID 1 is green, the next
SID,3
in this case would be green and forth.
Conditional is problematic, since the SIDs are not consecutive, this would
give the effect of odd/even. In this case SIDs both SIDs 1& 3 above would
be
green using the MOD argument.

Is this a little more clear?
Thanks

Bob Philips wrote:

A small tweak to my code to start at line 2 works fine

You can still do it with conditional formatting, you just need an
additional column to calculate values. I'm assuming the added column
is C, and the account numbers are in B. In C1, just put either "True"
or "False" (without quotes). In C2, enter:
=IF(B2<OFFSET(B2,-1,0),NOT(OFFSET(C2,-1,0)),OFFSET(C2,-1,0))
Fill down from C2.

Now in the conditional formatting, you just need the formula:
=$C2
or
=Not($C2)

This will work even if you resort your data.