Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

2 sheets of the same data

  Asked By: Adelisa    Date: Mar 03    Category: MS Office    Views: 690
  

Really hope somebody can help me out from these, non and vba solutions :



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.


Share: 

 

7 Answers Found

 
Answer #1    Answered By: Fadiyah Khan     Answered On: Mar 03

: 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.)

 
Answer #2    Answered By: Billie Young     Answered On: Mar 03

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?

 
Answer #3    Answered By: Friso Bakker     Answered On: Mar 03

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).

 
Answer #4    Answered By: Ula Hashmi     Answered On: Mar 03

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

 
Answer #5    Answered By: Horace Hernandez     Answered On: Mar 03

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?

 
Answer #6    Answered By: Hasad Yilmaz     Answered On: Mar 03

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

 
Answer #7    Answered By: Zakary Thompson     Answered On: Mar 03


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

 
Didn't find what you were looking for? Find more on 2 sheets of the same data Or get search suggestion and latest updates.




Tagged: