VBA change color of cell in a column, if duplicate data, except i condition

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
306
Hello and thanks for helping

I am trying to change the color of a cell in a column
to yellow if it is has duplicate data.
That part is done

HOWEVER
I want to don't want to change the color of any cell,
in that column, if it says "Monday"

so I guess all cells in a column with duplicate data,
Except cells with "Monday",
the cells should change to yellow


This is the code I have been using, and would like to adapt

Code:
Sub sbFindDuplicatesInColumn()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("F65000").End(xlUp).Row

For iCntr = 1 To lastRow
    If Cells(iCntr, 3) <> "" Then                   
         matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 3), Range("c1:c" & lastRow), 0)
   
' need something here to Don't do anything if the cells says Monday
    If iCntr <> matchFoundIndex Then       
        Cells(iCntr, 3).Interior.ColorIndex = 6

End If
End If
Next

Thanks again for helping

Thomas
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi,
I haven't broken my neck testing this out, but at a glance, (if I understand your desire), you might be able to get away with just changing this line (from):
If Cells(iCntr, 3) <> "" Then
to:
If Cells(iCntr, 3) <> "" And Cells(iCntr, 3) <> "Monday" Then

If that's not what you're looking to do, let us know and I'll put down the coffee and look further into it.

Hope it helps.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,429
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If I understand correctly what you want to do, I think you need a different approach. Your code has no error handling if there is no match and it doesn't seem to be selecting the correct cells to color fill.

Here's an alternative that may get you there:
Code:
Sub sbFindDuplicatesInColumn()
Dim lastRow As Long
Dim matchFoundIndex As Variant
Dim iCntr As Long
lastRow = Range("F65000").End(xlUp).Row

For iCntr = 1 To lastRow
    If Cells(iCntr, 3) <> "" Then
         matchFoundIndex = WorksheetFunction.CountIf(Range("c1:c" & lastRow), Cells(iCntr, 3))
         If matchFoundIndex > 1 And Cells(iCntr, 3) <> "Monday" Then
            Cells(iCntr, 3).Interior.ColorIndex = 6
        End If
    End If
Next iCntr
End Sub
 

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
306
JoeMo, HalfAce

Hello and thank you very much,

Nope, cells with Monday are still turning yellow

trying to over look the cells with Monday, and NOT change the cell color
of any cell with Monday.

I am hoping to keep the code as short as possible

although I may just add another loop to the code to change the cell color to, no color
if the cell equals Monday

Thomas
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

When I run your original code, I get all duplicates to turn yellow with the exception of the first instance of the value.
When I run it with the modification I posted above it does the same thing, except for any cells containing the value "Monday" which are ignored.

What results do you get when you try it?
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
Why not just use Conditional formatting?
Code:
=AND(C1<>"Monday",COUNTIF(C:C,C1)>1)
Choose yellow color for the formatting...
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,429
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

JoeMo, HalfAce

Hello and thank you very much,

Nope, cells with Monday are still turning yellow

trying to over look the cells with Monday, and NOT change the cell color
of any cell with Monday.

I am hoping to keep the code as short as possible

although I may just add another loop to the code to change the cell color to, no color
if the cell equals Monday

Thomas
The code I posted works fine for me - cells that have Monday do not get filled with yellow.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Thomas,
When I run Joe's code, it works for me as well. Highlighting all instances of dupes, ignoring those with "Monday".
I would be tempted to go with conditional formatting myself, which would produce the same results as Joe's code, but your original post mentioned the code as being what's in use and (as written), does not highlight the first instance, but only the 2nd or more of any duplicate values. I don't know if that's by design or not...

In either case I suspect there's something involved we're not aware of.
Do the cells with Monday contain anything else, (such as a date or something) or is "Monday" the entire value of the cell?
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,429
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Thomas,
When I run Joe's code, it works for me as well. Highlighting all instances of dupes, ignoring those with "Monday".
I would be tempted to go with conditional formatting myself, which would produce the same results as Joe's code, but your original post mentioned the code as being what's in use and (as written), does not highlight the first instance, but only the 2nd or more of any duplicate values. I don't know if that's by design or not...

In either case I suspect there's something involved we're not aware of.
Do the cells with Monday contain anything else, (such as a date or something) or is "Monday" the entire value of the cell?
To add to HalfAce's questions, if the cell contains only "Monday", could there be any spaces before or after the word "Monday"? To check, pick any cell with Monday in it (say it's C10 for this example) and in any empty cell enter: =LEN(C10). If that returns a value greater than 6 there are other characters in the cell than those found in "Monday".
 

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
306
JoeMo, HalfAce

Yes, your code works, it was all on me,
Monday is Capitalized on this worksheet

I got back in to it last night and finally saw my error

thank you both so much for your help, as always it
is greatly appreciated

Thomas
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,427
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top