Duplicates on other worksheets

[wrightyrx7]

Hi all, just posted a thread about 30 minutes ago, FIRST reply was exactly what i wanted. Love the forums!!!

Thought i would try another question. First let me explain what my spreadsheet is for-

I have a report for each month of the tax year. Every month we run a report which consits of roughly 11 columns, this displays about 75 employees each month. and then i have to vlookup these employee's to make sure they are not already on any of the previous months.

So lets say i run the report at the end of this month. I copy from my report and paste onto my Sep 11 worksheet next to all my previous months/worksheets.

Is there a vba i can run to check the other worksheets for duplicates an if there is, change the font colour to red?

Please aks if you need anymore information

Thanks in advance

Chris

 

[Trevor G]

Hi Chris,

Look at conditional formatting that uses CountIF>0 and then you should beable to set colours.

Are the employee names in the same Column all the time (I would assume so)

Do you want the activesheet (New month) to have the coloured cells?

Sample formula

=IF(COUNTIF(Sheet2!A:A,Sheet3!A:A)>0,"",A2)

 

[wrightyrx7]

Hi Trevor, thanks for replying.

Yes the data is in the same columns everymonth.

Yes the activesheet to have the coloured cells

Is this possible in conditional formatting?

Thanks again for the reply

Chris

 

[Trevor G]

This code will do what you want, it will only colour on Sheet 1

If you want to highlight the found names on the other sheets you would need to change the xlNone to another colour

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FindMultipleStaff()<br><SPAN style="color:#00007F">Dim</SPAN> wksht <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastrow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, icell <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> startaddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>lastrow1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> icell <SPAN style="color:#00007F">In</SPAN> Sheets(1).Range("A2:A" & lastrow1)<br><SPAN style="color:#00007F">For</SPAN> wksht = 2 <SPAN style="color:#00007F">To</SPAN> Worksheets.Count<br>    lastrow = Sheets(wksht).Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">Set</SPAN> Rng = Sheets(wksht).Range("A2:A" & lastrow)<br>    <SPAN style="color:#00007F">With</SPAN> Rng<br>    <SPAN style="color:#00007F">Set</SPAN> c = .Find(what:=icell.Value, LookAt:=xlWhole, LookIn:=xlValues, SearchDirection:=xlNext)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        icell.Interior.ColorIndex = 3<br>        startaddress = c.Address<br>        <SPAN style="color:#00007F">Do</SPAN><br>            c.Interior.Color = xlNone<br>            <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)<br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> startaddress<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> wksht<br><SPAN style="color:#00007F">Next</SPAN> icell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[wrightyrx7]

Worked like a charm, thanks for sparing the time to help me out.

Is the coding much different to change the font colour to red instead of the Cell colour?

This works perfect but thought i would ask.

Thanks again

Chris

 

[Trevor G]

Chris,

Change the word Interior to Font

icell.Font.ColorIndex = 3

 

[wrightyrx7]

Great work Trevor. Exactly what i wanted! Thanks Again

Chris

 

[wrightyrx7]

Is there anyway to get this to match 2 values on a row before it marks it as a duplicate?

The columns are C and M.


Thanks in advance

Chris