Hi Mangesh

Thank you for VB code. I have copied all the coding in sheet1, but nothing
seems to happen. Can you plz suggest me some tricks so that it should work
fine.

How do I set the colour for column A,B & C in sheet 2. I tried to do that
but didn't get through it. I want to set the colour format for each class.
Say for class A: cyan blue, CLass B: yellow, Class C: Magenta

Thanking you in anticipation!!



"Mangesh Yadav" wrote:

Since I already have a small code, I might as well post it.
Use this in the sheet1's event module:

Private Sub Worksheet_Change(ByVal Target As Range)

Set sht2 = Worksheets("Sheet2")
If Target.Column = 2 Then

Set rngA = sht2.Range(sht2.Range("A3"), sht2.Range("A3").End(xlDown))
Set rngB = sht2.Range(sht2.Range("B3"), sht2.Range("B3").End(xlDown))
Set rngC = sht2.Range(sht2.Range("C3"), sht2.Range("C3").End(xlDown))

Set v = rngA.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngB.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Set v = rngC.Find(Target.Value, LookIn:=xlValues)
If v Is Nothing Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngB(1).Offset(-1,
0).Interior.ColorIndex
End If
Else
Target.Interior.ColorIndex = rngA(1).Offset(-1,
0).Interior.ColorIndex
End If

End If

End Sub

With your table in sheet2, and the row 2 cells will have a background color
as indicated in the text above

Mangesh