excel (detecting duplicate data)

I have an excel workbook with multiple worksheets.
I want to be able to detect if any duplicate data has been inputed into all of the worksheets.
If this is detected, I want it to highlight the duplicate cell.
Is there a way to have this check the data after every entry?
I was fooling around with conditional formating and the countif function, but it is highlighting the wrong cells and I can not get it to detect data from other worksheets.
any help would be appreciated.
thanks.

You might attempt to sort the data after an entry is made, then look for the new entry and check to see if it already exists.

Depending on how closely you want to watch for duplicate data, you may look into using a database in stead of Excel.

Here is what I have done.
I think I can only do this in VBA Macro Editor.
Every time I execute the below I get a

Quote:

---

Compile Error:
Sub or Function not defined

---

and it points at the function header to the code below.
does anyone have any ideas?

Code:

---

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

For Sheet = 1 To 6

    Worksheet(Sheet).Activate
 
    totalRows = ActiveSheet.UsedRange.Rows.Count
   
    For Row1 = totalRows To 2 Step -1
   
        CellValue = Cells(Row1, 7).Value
       
        For Row2 = Row1 - 1 To 2 Step -1
   
            If CellValue = Cells(Row2, 7).Value And CellValue <> "" Then
           
                Cells(Row2, 7).Color = RGB(255, 0, 0)
                         
            End If
       
        Next Row2
    Next Row1

Next Sheet

End Sub

---

here is the new code.
please someone take a look and tell me what i'm doing wrong thanks.

it compiles with no errors, but i have a logic problem.

Code:

---

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
 
    If ActiveCell.Value <> "" Then
 
    Dim Sheet1 As Integer
    Dim totalRows1 As Integer
    Dim Row1 As Integer
    Dim Sheet2 As Integer
    Dim totalRows2 As Integer
    Dim Row2 As Integer
       
    CellValue = ActiveCell.Value
               
    For Sheet1 = 1 To 6
       
        totalRows1 = Worksheets(Sheet1).UsedRange.Rows.Count
        For Row1 = totalRows1 To 2 Step -1
               
            For Sheet2 = 1 To 6
                                       
                totalRows2 = Worksheets(Sheet2).UsedRange.Rows.Count
                   
                For Row2 = totalRows2 To 2 Step -1
   
                    Worksheets(Sheet2).Cells(Row2, 8).Value = Worksheets(Sheet2).Cells(Row2, 7).Value
                   
                    If CellValue = Worksheets(Sheet2).Cells(Row2, 7).Value Then
                        Worksheets(Sheet2).Cells(Row2, 7).Interior.ColorIndex = 15
                       
                    Else
                        Worksheets(Sheet2).Cells(Row2, 7).Interior.ColorIndex = 40
                    End If
                                                                           
                Next Row2
               
            Next Sheet2
           
        Next Row1
    Next Sheet1
   
    End If
End Sub

---

I don't see why do you need 2 loops if the Sheet1 is not used at all

Could you explain your algorithm in English?
Like
1. Take the new entered value
2. For every sheet in the document
3. For every line in the sheet
4. Take value from the column 7
5. etc

doesn't access have a query function that does that.

here is my most recent code with comments
sorry this project is exclusively for excel.
i'm not allowed to use access.
i wish i could use VB, but that isn't an option either.
basically this is what is going on.
i have a spreadsheet that has rows of data.
i'm trying to make sure no duplicates are inputed into the spreadsheet.
i the subroutin below with a few logic errors in it.
it searches the sheet for duplicates, and once it does it searches through it again to highlight all duplicates. i want it to do this as i press enter after entering the data that is needed into the worksheet, but it only works if i click on the cell after the data was already entered. does anyone know of an event that triggers when you press enter in a excel cell? i was unable to identify it in the list the vba editor gives me.

Code:

---

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
    If ActiveCell.Value <> "" Then  'check to make sure cell is not null
 
        Dim Sheet As Integer        'loop counter for sheets
        Dim totalRows As Integer    'counter for total rows
        Dim Row As Integer          'row counter
        Dim dupCount As Integer      'duplicate cell counter
   
        dupCount = 0                'set to zero
       
        CellValue = ActiveCell.Value  'aquire data in selected cell
           
           
        'nested loop determines any duplicates
        For Sheet = 1 To 6        'for loop to cycle through worksheets
            totalRows = Worksheets(Sheet).UsedRange.Rows.Count  'acquire total rows on current worksheet
            For Row = totalRows To 2 Step -1    'for loop to cycle through rows in the worksheet
                If CellValue = Worksheets(Sheet).Cells(Row, 7).Value Then 'if duplicate found increment dupCount
                    dupCount = dupCount + 1    'increment dupCount
                End If
            Next Row
        Next Sheet
             
        Worksheets(Sheet - 1).Cells(Row + 1, 8).Value = dupCount 'post dupCount for debuging
   
        'nested loop colors all duplicates
        If dupCount > 1 Then
            For Sheet = 1 To 6        'for loop to cycle through worksheets
                totalRows = Worksheets(Sheet).UsedRange.Rows.Count  'acquire total rows on current worksheet
                For Row = totalRows To 2 Step -1    'for loop to cycle through rows in the worksheet
                    If CellValue = Worksheets(Sheet).Cells(Row, 7).Value Then 'if duplicate found increment dupCount
                        Worksheets(Sheet).Cells(Row, 7).Interior.ColorIndex = 3 'paint cell red with duplicates found.
                    End If
                Next Row
            Next Sheet
        End If
    End If

End Sub

---