This post is in continuation to First one i.e. Restricting User to enter Duplicate Values using Data Validation. As the previous Duplicate Restrictor has some limitations. So I came up with a rather new way ( Conditional Formatting ) to achieve it. This time, it will highlight the duplicate cell, that is too, except First Occurrence. Here is the Conditional Formatting formula:- =COUNTIF($C$1:$C1,$C1)>1 Here are the steps:- 1. Select the Target Column say C ( in the attached example ) 2. Go to Home Tab--> Conditional Formatting ---> New Rule --> Formula 3. Type the above formula there. 4. Select the formatting for highlighting the duplicates found like filing colour in cell or colouring Font. ( I have used Green Colour in the attached example ) 5. Click OK. Explanation:- The Target column which has been selected, is the Range on which Conditional formatting applies. Excel interprates above formula in context of Each cell in the Target Range
Custom Excel Solutions.. ( Excel Functions and Vba )