Format an entire row when an ID changes

[Access Joe]

Hi,

Hoping someone can help. I have a list of Patient ID's in Column A.
Sometimes the ID will appear once, maybe twice, sometimes as much as 5 or 6
times. An example:

COLUMN A
101
102
102
102
103
103
104
105
105

What I want Excel to do is automatically shade the entire row every time the
number changes in this list. For example, make all the 101 records RED, the
102 records green, the 103's yellow, the 104's blue, etc. etc. I'm familiar
with conditional formatting, but am not sure it this would be the way to go.

Can anyone help?

Thanks! Joe

 

[Joel]

Joe use this work sheet change function
to install do the following
1) copy subroutine below from:Sub to:End Sub
2) go to tab on bottom of worksheet that normal has Sheet1
3) right click and choose View code
4) Paste function in VBA window
5) Make sure the > character is remvoed that is sometimes added at this
website on each line.
6) Any place in column a put in a number between 101 and 104.

Sub worksheet_change(ByVal Target As Range)

If Target.Column = 1 Then
LastColumn = Target.End(xlToRight).Column
Set Myrange = Range(Cells(Target.Row, 1), _
Cells(Target.Row, LastColumn))
With Myrange

Select Case Target

Case 101
.Interior.ColorIndex = 3
Case 102
.Interior.ColorIndex = 4
Case 103
.Interior.ColorIndex = 6
Case 104
.Interior.ColorIndex = 41

End Select

End With

End If


End Sub

 

[Access Joe]

Thanks so much

Joe use this work sheet change function
to install do the following
1) copy subroutine below from:Sub to:End Sub
2) go to tab on bottom of worksheet that normal has Sheet1
3) right click and choose View code
4) Paste function in VBA window
5) Make sure the > character is remvoed that is sometimes added at this
website on each line.
6) Any place in column a put in a number between 101 and 104.

Sub worksheet_change(ByVal Target As Range)

If Target.Column = 1 Then
LastColumn = Target.End(xlToRight).Column
Set Myrange = Range(Cells(Target.Row, 1), _
Cells(Target.Row, LastColumn))
With Myrange

Select Case Target

Case 101
.Interior.ColorIndex = 3
Case 102
.Interior.ColorIndex = 4
Case 103
.Interior.ColorIndex = 6
Case 104
.Interior.ColorIndex = 41

End Select

End With

End If


End Sub