+ Reply to Thread
Results 1 to 2 of 2

Conditional formatting for duplicates across multiple sheets

  1. #1
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Exclamation Conditional formatting for duplicates across multiple sheets

    Hello all,

    I'm still very new to the advanced functions of Excel and have spent hours on Google trying to find a formula/rule/macro that I can adapt, but have been unsuccessful, so i'm not sure if what I need is even possible...

    Simply put: I'm trying to conditionally format (or similar) across multiple sheets in the same workbook.

    I have attached a sample workbook to illustrate.

    The "Archive" sheet will be just that, accumulating data daily.

    The "Paste" sheet will be completely cleared and have a new set of data put in each day (always the same columns/rows/formatting).

    I am trying to get excel 2010 to check all of column B ( so =$B:$B) in the "Paste" sheet, automatically when I paste data, against all of column B in the "Archive" sheet (which will progressively get larger). If a duplicate is found, highlight (or some indication) the cell on the "Paste" sheet only.

    Thank you all in advance for any feedback. I'm sure there are others that would be interested in a solution as well.
    Attached Files Attached Files
    Last edited by adtc; 08-09-2011 at 11:42 PM. Reason: original query was too complex

  2. #2
    Registered User
    Join Date
    08-01-2011
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Conditional formatting for duplicates across multiple sheets

    I think I have found a partial solution is a Macro from the following website, for anyone that is interested: http://www.familycomputerclub.com/fi...-ms-excel.html

    The code is below. You will need to adapt it for your page names ("Sheet1") and ranges ("B:B").

    Can anyone please help me with additional code to make the macro: when TRUE paste to a 3rd sheet in a specific range. I.e. under a fixed heading, and in next empty cell.

    Thanks.

    Here's the code:

    Sub findDuplicates()
    ' code to find duplicates in 2 different worksheets
    Dim rng1, rng2, cell1, cell2 As Range
    ' 4 ranges have been defined
    Set rng1 = Worksheets("Sheet1").Range("B:B")
    'rng1 defines the existing data in column B and worksheet1
    Set rng2 = Worksheets("Sheet2").Range("D:D")
    'rng2 defines the imported data in column D and worksheet2
    For Each cell1 In rng1
    If IsEmpty(cell1.Value) Then Exit For
    'check for empty rows. If true then exit the program
    For Each cell2 In rng2
    If IsEmpty(cell2.Value) Then Exit For
    If cell1.Value = cell2.Value Then
    'compare data in cell1 and cell2 and then format if they have equal values.
    cell1.Font.Bold = True
    cell1.Font.ColorIndex = 2
    cell1.Interior.ColorIndex = 3
    cell1.Interior.Pattern = xlSolid
    cell2.Font.Bold = True
    cell2.Font.ColorIndex = 2
    cell2.Interior.ColorIndex = 3
    cell2.Interior.Pattern = xlSolid
    End If
    'run the looping process
    Next cell2
    Next cell1
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1