Repeat the macro every 15 sec after opening the excel file

[mq2022]

Hello evryone,

I would like to have a duplicate checks on a column ( C column in my case) repeating itself every 15 seconds at the opening of the excel file or each time the user add a data into the column ( C column after the opening )

I know that for the macro to start executing at the opening, I have to put it in " This workbook" MEO.

The timer can be done with sth like this application.OnTime When:=Now + TimeValue("0:" + "0" + "30")

For the repeating part, I read that I could use a GoTo or do an infinite loop

The Macro that I would like to repeated is a duplicate check in the columns
- If duplicate, the rows should be highlighted in red.
- If the duplicate is removed, the rows should not be highlighted anymore.

Sub dupli_check()

Dim xMin As String

repeat:

Dim LastRow As Long

ActiveSheet.Select

LastRow = ActiveSheet.UsedRange.Rows.Count

For r = 6 To LastRow

If WorksheetFunction.CountIf(Range("C6:C" & LastRow), Cells(r, 3).Value) > 1 And Cells(r, 3).Value <> "" Then
Rows(r).Interior.ColorIndex = 3

ElseIf WorksheetFunction.CountIf(Range("C7:C" & LastRow), Cells(r, 3).Value) < 1 Or Cells(r, 3).Value = "" Then
Rows(r).Interior.ColorIndex = 2

Next r

Else

Application.OnTime Now() + TimeValue("0:" + "0" + "30"), "repeat"

GoTo repeat

End Sub

I'm a bit lost. Could you help me figure where is my error ? Thanks you for you time and help

Br,
Martin

[CheeseSandwich]

You could use conditional formatting to highlight duplicates - it will be live all the time the workbook is open?

I would say that having code fire every 15 seconds will make using the spreadsheet rather annoying.

[mq2022]

Hi CheeseSandwich,

For now I'm using condtionnal formatting but I have to use 2 rules. If I only use one, the rows that are empty in my selection are also highlighted in red because empty rows are duplicates.

So I have 2 rules "fighting" each other.

First is the one to highlight in red rows that contain a duplicate
2 snd is the one putting in blanck rows that don't have data in that cell

With that, each time I add data in this colum ( C column) I have a small delay.

Also I would like this conditionnal formatting to be applied at several sheets.

[CheeseSandwich]

So if I was looking for duplicates and wanted to ignore blanks I might use a formula in the conditional formatting box like:

Please Login or Register  to view this content.

I would then apply that conditional formatting to the whole of column A in the above example.

Try it, select column A and add CF and use the formula above and then put some duplicates into column A

[CheeseSandwich]

You can also extend the range to be highlighted, see attached.

[mq2022]

It would work if the column I'm checking is only data. In my case, the column i'm checking is a formula concatenating the data of 2 columns. But you showed the way. I will use a second column that I will hide and having this formula ( IF(A1<>"",COUNTIFS($A$1:$A$10000,$A6),VALUE(1)).

My MFC will be on that last column having a basic formula $A1>1

Thanks for your help I still be working on the macro without you