Code to search through a range?

[Firestart]

Hi,

I would like to know the code to search through a range. If a single cell’s content in the range is the same as a cell within a range on another workbook, highlight the cell in green, and add one to an integer and display the total when the loop has finished.

Can anyone help with that?

Thanks

[Ecniv]

You'd need to pass it the two ranges (assuming it is only two ranges).
then loop through the cells grabbing the one value and then using
1) a range then .Find on that range object
2) a range specified and a manual loop through columns and rows of cells comparing each

Have you considered using a pivot table on the secondary list to provide a column and a count of occurances, then a vlookup or code to match and return this?

[Firestart]

How would I do all of the above. I am a novice

[Ecniv]

First lets take a normal range.

Highlight a range on a new workbook.
Tools>Macro>VB Editor
Press Ctrl+G
Type the following (first line then the second):

?selection.address

?selection.address(true,true)

Any information wanted on the selection object or the address method (?) put the editing cursor in them and press f1.


Say you wanted to loop through the range (just a simple range for now).
It would probably be best to get an object of the range and hold a current row/col pointers in variables.

public sub testme()
    Dim rng as Range
    dim lngRow as long, lngCol as long

'    On Error Resume Next 'Read up on error handling

    set rng = selection.range

'---- Clean up (these are remarks to help you understand how you do things
'----   They are useful so write them out!!)
    set rng = nothing
end sub

That is the start. You then need to look at how to get the start and end columns and rows so you know how much to loop. There is a function to change the address to R<num>C<num>:R<num>C<num> then you can use text manipulation to get the start and end cols and rows.

Have an experiment first and use the help files. Also, when you have objects defined properly, the intellisense can be useful.

Alternatively, use Tools>Macros>Record Macro, and see what it outputs as you go around the sheets editting.

Post up once you've tried something.

[Firestart]

Sorry, I should have put beginner!

This is what I was thinking. Do I have the right concept?

'For Each luCell in sheet1
'search through a range.


'If a single cell’s content in sheet1 (Overall sheet) range

'is the same as a cell within a range on another sheet (other sheet),

' THEN highlight the cell in green (on the Overall sheet),
'ActiveCell.Interior.Color = RGB(?,?,?)


'Next luCell