Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

highlighting for not duplicates items

0

Hi

I  try  writing  macro   to  highlight for   not  duplicates  items , but  the  code  doesn't  work  at  all and  doesn't  show  any  error .

the  code  should  not  highlight item is  repeated more  than one  time, otherwise  should  highlight it 

can  anybody  tell  me  what  I have  to  do  to  work?

Sub Macro2()
Dim lr, fn As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
fn = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), Sheet1.Range("A" & i).Value)
 If fn < 1 Then
 Sheet1.Range("A" & i).Interior.ColorIndex = vbRed
  End If
 Next i

End Sub
Answer
Discuss

Answers

0
Selected Answer

Speed

In your test, variable fn can never be  < 1 since the CountIf function will always return 1 or more (since the cell itself is at least one instance of its own value in column A).

You just need to make the changes in bold below (done in the attached file):

Sub Macro2()
Dim lr, fn As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lr
    fn = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), Sheet1.Range("A" & i).Value)
    If fn = 1 Then Sheet1.Range("A" & i).Interior.ColorIndex = 3
Next i

End Sub

then all unique values will get a red fill (but others will remain as they were). You can restore the block If (should you need to do more than fill the cell) but currently it's okay on one line.

This is because ColorIndex has colours numbered 0-56 (plus automatic and none) where 3 is red. 

Alternatively, you could use the line:

If fn = 1 Then Sheet1.Range("A" & i).Interior.Color = vbRed

Hope this helps.

Discuss

Discussion

OMG!
bad error !
thanks  john  for   your  correction.
speed (rep: 40) Feb 22, '23 at 2:07 pm
No problem - we all make mistakes, Speed (that's how we learn!) Thanks for selecting my Answer.
John_Ru (rep: 6142) Feb 22, '23 at 2:48 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login