: I have 2 sheets, where if I key  in any numbers  into a list  of the first
: sheet, the same numbers in the second sheet  will be highlighted and can
: be counted too column  wise.

What do you mean by "column wise"?

Are the numbers in the first sheet matching more than one cell on the
second sheet? (If it is a one-to-one match, then you only need to count
the list on the first sheet. No need for the second sheet.)

Yes , it's a one-to-more matching to the second sheet...but anybody
knows how can the same numbers  within the workbook is highlighted when
we key  in any numbers in the first sheet?

This seems to work

Private Sub Worksheet_Activate()
Dim c, x As Range

For Each c In ActiveSheet.UsedRange
If Not c = "" Then
Set x = Worksheets(1).UsedRange.Find(c.Value)
If Not x Is Nothing Then
c.Interior.Color = RGB(0, 255, 255)
End If
End If
Next c
End Sub

I am concerned that is there are a lot of numbers  in each sheet  it may be
slow.
It works when the second sheet is activated. i.e. when you switch to it.

If you have both sheets  showing in two windows at the same time it will not
result in instant changes to sheet 2. They will only happen when you click on
it.

I cannot spend the time now to work out how to get the matched column  totals.
I think you would need to build a function to count the cells in the column
with Interior.Color = RGB(0, 255, 255).

>> I cannot spend the time now to work out how to get the matched column
totals.
I think you would need to build a function to count the cells in the column
with Interior.Color = RGB(0, 255, 255).

That's one function I do have available in my Code Library....

Count or Sum Cells based on Background Color
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As
Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) +
vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

still I stuck with below:



1. If I delete the numbers  in sheet  1, the color still remain in
sheet 2 , how to avoid this ?
2. It looks like it will find any numbers or part of the numbers
and even header or formula that have the same value to be highlighted ,
how to avoid this and only refer to the numbers that we key  in only?

Try this change (added line beginning "ActiveSheet")

Private Sub Worksheet_Activate()
Dim c, x As Range
ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone
For Each c In ActiveSheet.UsedRange
If Not c = "" Then
Set x = Worksheets(1).UsedRange.Find(c.Value)
If Not x Is Nothing Then
c.Interior.Color = RGB(0, 255, 255)
End If
End If
Next c
End Sub

finally got it . I change the Find(c.value) to Find(c,
Matchbyte:=xlValue) to avoid part of the numbers  to be find