check duplicate file name and mark it with red!

[newapa]

Hi!
I was trying to check the file name in 2 column.
And if it duplicate it should be mark green. and if it not duplicate it should be mark red.
something like this.

a.xlm b.xlm<---- green
b.xlm a.xlm<---- green
c.xlm <-----red

but it marks every file name with gren. it should be about 60 rows with red.

Sub SubControll()
    Dim ws As Worksheet
    Dim i As Integer
    Set ws = ThisWorkbook.Sheets("Controll")
    
    For i = 2 To ws.range("B2").End(xlDown).row
       Exist ws, ws.Cells(i, 2)
    Next i
End Sub


Sub Exist(ws As Worksheet, Name As String)
    Dim i As Long
    
    For i = 2 To ws.range("A2").End(xlDown).row
        If Name = ws.Cells(i, 1) Then
            ws.Cells(i, 1).Interior.ColorIndex = 3
        Else
            ws.Cells(i, 1).Interior.ColorIndex = 4
        End If
    Next i
End Sub

thx in advance

 

[Andrew Poulsom]

You could use Conditional Formatting for that, but if you want a macro:

Sub Test()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Controll")
    For i = 2 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        With ws.Cells(i, 1)
            If WorksheetFunction.CountIf(ws.Columns(2), .Value) Then
                .Interior.ColorIndex = 4
            Else
                .Interior.ColorIndex = 3
            End If
        End With
    Next i
End Sub

 

[Trevor G]

This will work.

[FONT=Courier][COLOR=#00007F]Sub[/COLOR] checkCell()
Sheets("Controll").Select
Range("b2").Select
[COLOR=#00007F]Do[/COLOR] [COLOR=#00007F]Until[/COLOR] ActiveCell.Value = ""
[COLOR=#00007F]If[/COLOR] ActiveCell.Value = ActiveCell.Offset(0, 1) [COLOR=#00007F]Then[/COLOR]
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Offset(0, 1).Interior.ColorIndex = 3
[COLOR=#00007F]Else[/COLOR]
ActiveCell.Interior.ColorIndex = 4
[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
ActiveCell.Offset(1).Select
[COLOR=#00007F]Loop[/COLOR]

[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub[/COLOR]
[/FONT]

 

[newapa]

Hi guys!

Thx for u code.
but i have been struggling with this code the hole day now.
And i want to find out why, what is going wrong and how can i change it?
to get the code work.

i just wanted to learn so i know what i was missing till the next time i get into this situation.

 

[Andrew Poulsom]

You weren't exiting the loop when a match was found. So unless the match was on the last row the cell would be coloured green. In addition you were checking each entry in column A against an entry in column B multiple times. So what may previously have been coloured red will change to green when it's not equal to Name on a subsequent pass.

 

[newapa]

Thx Andrew!

i solved it.
i removed else ws.Cells(i, 1).Interior.ColorIndex = 4
and put the ws.Cells(i, 1).Interior.ColorIndex = 4 outsite the loop insted. and it's woring now.