Excel 2007 : Check 4 duplicate & Copy format & values of orginal's row over dupicate's row

[ice_cool1]

Is it possible for human race to come up with macro for excel 2007
which can check for duplicate and copy the format and whole row of original and paste it over duplicate/ duplicates.

Question for Advance Gurus:

Can i set range of cell to be copied from orginal to duplicate/duplicates
e.g if i want to copy format and values of first 3 cells of orginal and paste it over duplicate.

Example:

Orginal list
------------
happiness (Blue Color)
Wealth
Love (Red color)
Friendship
Success

New List:
---------
Greed
lust
love
money
happiness

Note:
there is dulplicate of love and happiness. Is it possibe for MACRO in Excel 2007 to copy the format of orginal i.e love (blue color) and paste it over duplicate, so both duplicate have same format.i.e font, color as of original.

thanks in Advance

Tia jOY

[pike]

Hi ice_cool1
welcome to the forum

Option Explicit

Sub ptest()
    Dim c, rngLook As Range, xItem, xColour As Integer
    Dim firstAddress As String
    Set rngLook = ActiveSheet.Range("A1:A3")
    With ActiveSheet.Range("A1", Cells(Rows.Count, 1).End(xlUp))
       For Each xItem In rngLook
        
        Set c = .Find(xItem, Lookin:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            xColour = c.Font.ColorIndex
            Do
               c.Font.ColorIndex = xColour
                         Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    Next
    End With
End Sub

[ice_cool1]

Hi Pike,

Thanks for Warm Welcome and kind help.

I Read your your message the moment you posted it

I Know little about Excel VBA and i don't understand your code.

I feel like being pest to ask you to explain the code.

I pasted you code in VBE and run it but nothing happens.

I know i m doing come thing wrong.

Pike,

1# I was given 100's of sites(column B) multiple times daily, i have to check and type their Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G)

2# Many times, when i got new list, It have many sites which i have recently checked for Page Rank etc.

3# To save my time, I try duplicate check. Means if i there is any dulpicate site, it shows "False" value in column A. I am using this formula =ISNA(VLOOKUP(B870,$C$1:C869,1,FALSE))

4# This Help me out to extent that i know which site is duplicate and i just go to original site and copy its page rank, keywords etc in front of duplicate.

Note: Since I need to report different sets of sites to different clients, I have put values in front of Dulpicate, which i do manually

5# I need Guru who can give me VBA code, which i copy and paste in VBE and simply run it.

6# It will very helpful if, i can select say 100 range of cells containing new sites(column B e.g B501:B600), and once i run this VBA Code, it checks range (B1:B599), and if it find that new sites have any duplicates. It Simply copy the values, of Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G), and paste in front of duplicate.

7# There are some sites whose page rank is lower than required. So i simply highlight those sites with Red and the sites whose page rank is according to requirement, i highlight them to green.

Note: Its Green highlighted sites who have values of Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G).

Is it possible that VBA code can copy the Color of orginal and highlight the duplicate with that color

I have start learning VBA, I Promise that I Will help people on this forum.

Thanks In Advance

I will remember your help

God bless you pike

Tia jOY

[ice_cool1]

Hi ice_cool1
welcome to the forum

Option Explicit

Sub ptest()
    Dim c, rngLook As Range, xItem, xColour As Integer
    Dim firstAddress As String
    Set rngLook = ActiveSheet.Range("A1:A3")
    With ActiveSheet.Range("A1", Cells(Rows.Count, 1).End(xlUp))
       For Each xItem In rngLook
        
        Set c = .Find(xItem, Lookin:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            xColour = c.Font.ColorIndex
            Do
               c.Font.ColorIndex = xColour
                         Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    Next
    End With
End Sub

Hi pike,

Kindly help me out on this one, I will return your favor sure

i m really stuck on this one

thanks in advance

Tia jOY