I get a daily report which I would like to make easier to read b
highlighting each set of row data alternately in different colour
according to the value in the first column

I.e.

a
a
a
a
b
b
b
b
c
c
d
d
d

All the rows where A = "a" would be in grey, then where A = "b", n
colour, then where A ="c" would be back to grey again and so on.

Much appreciate any help or advice available on this.

T

--
Message posted from http://www.ExcelForum.com

TW,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub AlternateGreyBasedOnColumnA()
'Written by Bernie Deitrick
Dim myCell As Range
Dim IsGrey As Boolean

IsGrey = False

For Each myCell In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If myCell.Row = 1 Then
With Intersect(Rows("1:1"), ActiveSheet.UsedRange)
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
IsGrey = True
End With
End With
Else
If myCell.Value < myCell(0).Value Then
With Intersect(myCell.EntireRow, ActiveSheet.UsedRange)
With .Interior
.ColorIndex = IIf(IsGrey, xlNone, 15)
.Pattern = xlSolid
IsGrey = Not IsGrey
End With
End With
Else
With Intersect(myCell.EntireRow, ActiveSheet.UsedRange)
With .Interior
.ColorIndex = IIf(IsGrey, 15, xlNone)
.Pattern = xlSolid
End With
End With
End If
End If
Next myCell

End Sub



"twaccess " wrote in message
...
I get a daily report which I would like to make easier to read by
highlighting each set of row data alternately in different colours
according to the value in the first column

I.e.

a
a
a
a
b
b
b
b
c
c
d
d
d

All the rows where A = "a" would be in grey, then where A = "b", no
colour, then where A ="c" would be back to grey again and so on.

Much appreciate any help or advice available on this.

TW


---
Message posted from http://www.ExcelForum.com/

Fantastic Bernie !

It works a treat, I'm going to add it to another macro which works o
the same report by removing unwanted columns and resizing column
etc...

Good to hear from you again.

Regards


Terr

--
Message posted from http://www.ExcelForum.com

Bernie..

a bit shorter wouldn't do it any harm :)

Sub AlternateGreyBasedOnColumnA()
'Alternative by keepITcool
Dim r&, Toggle As Boolean
With ActiveSheet.UsedRange
For r = 1 To .Rows.Count
With .Rows(r).Interior
If Toggle Then
.Pattern = xlNone
Else
.ColorIndex = 15
End If
End With
If .Cells(r, 1) < .Cells(r + 1, 1) Then Toggle = Not Toggle
Next
End With

End Sub

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bernie Deitrick" <deitbe @ consumer dot org wrote:

Sub AlternateGreyBasedOnColumnA()
'Written by Bernie Deitrick
Dim myCell As Range
Dim IsGrey As Boolean

IsGrey = False

For Each myCell In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If myCell.Row = 1 Then
With Intersect(Rows("1:1"), ActiveSheet.UsedRange)
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
IsGrey = True
End With
End With
Else
If myCell.Value < myCell(0).Value Then
With Intersect(myCell.EntireRow, ActiveSheet.UsedRange)
With .Interior
.ColorIndex = IIf(IsGrey, xlNone, 15)
.Pattern = xlSolid
IsGrey = Not IsGrey
End With
End With
Else
With Intersect(myCell.EntireRow, ActiveSheet.UsedRange)
With .Interior
.ColorIndex = IIf(IsGrey, 15, xlNone)
.Pattern = xlSolid
End With
End With
End If
End If
Next myCell

End Sub

keepITcool

I was waaaay too busy to optimize the code - just got it to work, as it
looked like there wasn't going to be a response....

Bernie

a bit shorter wouldn't do it any harm :)

Thanks Guys

Routine is embedded in my report tidy up macro complete with button o
my menu bar too.

All is working just fine too, I've just run it on a report I receive
this morning.

Regards


Terr

--
Message posted from http://www.ExcelForum.com