Hi,
I have 1 workbook which consists of 4 worksheets. The data in these
worksheets are names of people. Does anybody has a macro (or some ways)
to compare these 4 worksheets and highlight those names that exists in
all 4 worksheets ?
thanks

you could go to the first worksheet and next to the list of people you could
enter a formula like

ex: Cell B2
=if(sum(--(countif(Sheet2!A:A,A2)0),--(countif(Sheet3!A:A,A2)0),--(Countif
(sheet4!A:A,A2)0))=3,"All 4","No")

then drag fill this down the column. This assumes testing in Column A, but
you could choose more columns if need be.

--
Regards,
Tom Ogilvy

"Lynn" wrote in message
oups.com...
Hi,
I have 1 workbook which consists of 4 worksheets. The data in these
worksheets are names of people. Does anybody has a macro (or some ways)
to compare these 4 worksheets and highlight those names that exists in
all 4 worksheets ?
thanks

can i create a button for this formula so that i do not have to
remmember it and type it out everytime when i need to use it?

Sub AddFormula()
With Worksheets("Sheet1")
set rng = .range(.Cells(2,1),.Cells(rows.count,1).end(xlup))
End With
rng.offset(0,1).Formula = "=if(sum(--(countif(Sheet2!A:A,A2)0)," & _
"--(countif(Sheet3!A:A,A2)0),--(Countif(sheet4!A:A,A2)0))" & _
"=3,""All 4"",""No"")"
End Sub

--
Regards,
Tom Ogilvy


"Lynn" wrote in message
ups.com...
can i create a button for this formula so that i do not have to
remmember it and type it out everytime when i need to use it?

Tom,
how to use this AddFormula ?

Here are some resouces for learning about programming in VBA.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://web.archive.org/web/200312040...01/default.asp


http://support.microsoft.com/support...01/default.asp
Microsoft Excel for Windows -- Visual Basic for Applications 101


http://www.mvps.org/dmcritchie/excel....htm#tutorials

--
Regards,
Tom Ogilvy


"Lynn" wrote in message
oups.com...
Tom,
how to use this AddFormula ?

How would the code for this be changed if comparing 2 excel documents? I need
to compare two spreadsheets and delete rows on a large list based on tentries
occurence on another list.

"Tom Ogilvy" wrote:

Sub AddFormula()
With Worksheets("Sheet1")
set rng = .range(.Cells(2,1),.Cells(rows.count,1).end(xlup))
End With
rng.offset(0,1).Formula = "=if(sum(--(countif(Sheet2!A:A,A2)0)," & _
"--(countif(Sheet3!A:A,A2)0),--(Countif(sheet4!A:A,A2)0))" & _
"=3,""All 4"",""No"")"
End Sub

--
Regards,
Tom Ogilvy


"Lynn" wrote in message
ups.com...
can i create a button for this formula so that i do not have to
remmember it and type it out everytime when i need to use it?