Alternate row highlighting with a difference

T

twaccess

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
 
B

Bernie Deitrick

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
 
T

twaccess

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
 
K

keepITcool

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 >
 
B

Bernie Deitrick

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
 
T

twaccess

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
 
Top