Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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

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

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

Related Topics:

- Retrieve data from Sheet with SQL and put it in Another sheet
- unprotect the sheet, insert hyperlink & then protect the sheet
- UDF Works only in One Sheet, Not in Other Sheets
- List in one sheet unanswered questions in checklist sheet
- other sheet's active cell
- MERGE TWO SHEET IN ONE NEW SHEET
- compare data in two sheets
- compare data between 2 sheets
- Opening Drill Down Data of Pivot Table in Single Sheet
- copying data from one sheet to another
- Retriving data in a userform from a excel sheet
- compare data in two sheets
- compare data between 2 sheets
- Transferring excel sheet data into array
- how pass data form excel sheet to sql db
- move data from 90 odd sheets.
- Add new Sheet and Copy data
- Copying data from an inactive work sheet
- reconcile data from samew workbook but in different sheet
- Lookup on a sheet so that every time when file open it copes data from another file sheet
- Lookup Sheet
- Hide sheets
- Delete VB/Macro Sheet
- Excel User Form and Active Excel Work Sheets Question
- renaming sheets