Validation and Highlighting & copying columns, using VBA

[Truculent Llama]

Hi all,
I have a very large spreadsheet (currently at 130k+ rows and growing), which I'd like to perform a few functions on:
1. validate the fields within one of the columns, and alert the user if there are invalid entries
2. remove duplicate entries, keeping bottom rows instead of top rows

Request #1
My list of valid values to compare against is 136 values.
I have sought help from other users on Reddit r/excel and have had lots of help, however the validation involved a looping solution (and another a loop within a loop) to check through each cell in the column, and compare it against the list of valid options. There were all sorts of issues I encountered because of the large data set, and in the end I ended up settling on Excel conditional formatting to highlight invalid cells (which by comparison is instantaneous), and then sorting the data based on coloured fill. My macro also selects the last cell in the column, hopefully drawing the user's eye to the shaded cells. So far so good.
What I would like is for a quick VBA script that can assess whether or not any of those cells have been conditionally formatted, and if so throw an error message to alert the user. The MsgBox I'm fine with. It's the checking for the error that I can't work out.

Request #2
The way the data is managed, sometimes we need to overwrite certain rows with new data. Using a solution like VLOOKUP would be too cumbersome for the workflow in place, so what I'd like to implement is a simple solution to create a helper column to sort based on "newest" to "oldest", then simply remove duplicates top to bottom.
The data does not contain date or time columns. What I thought might be the easiest would be to:
1. Helper column has a simple count (+1 based on the row above)
2. Turn off auto calculation
3. Force a recalculation on the helper column with Application.Calculate
4. Sort the data descending
5. Remove duplicates
6. Turn auto calculation back on

So I think during the writing of this post, I've basically answered my own Request #2, but would love some help with Request #1.
Having said that, if anyone has any great suggestions for either, I'm open to ideas.

Thanking you in advance,
Llama

 

[Truculent Llama]

Won't work if there are less than 300 rows ..
BTW worked fine for me

Michael I've been working on integrating and implementing your code into mine, and I seem to have run into some unexpected errors.
1. If someone fiddles with the rows within the table, it breaks the references in the counter helper column which I'm using to sort in reverse order, and remove oldest duplicates first.
The solution I implemented was to simply name my column header "0" then for each subsequent row +1 to the cell above. However if someone deletes rows or inserts rows, the references and formulas break.
I understood that tables were supposed to intelligently update references when such changes were made?
On further investigation, it appears it's pasting an "inconsistent formula" and I can right click and correct it, but I didn't think I would need to, and I certainly don't want the end user to have to rely on this.
I've found this Mr Excel post that implied (at least back in 2014) that it was not possible. Has the situation possibly changed since then?
Or might there be a better way for me to remove older duplicates instead of newer ones?

2. On final testing, it appears the conditional formatting formula (MM1) does not work as desired. It is checking whether the cell has a conditional formatting rule, not whether or not that conditional rule applies. Thus, it's returning every single cell in the row as "invalid", when this is not the case.

 

[Michael M]

Well it depends on what the CF rule is !
If it's a colour change of the cell or Font, then a code can do that, but we would need to know Exactly what that colour is ?
Very hard to control people that fiddle.....apart from locking worksheets, then running the code with an unprotect built into that code

 

[Truculent Llama]

Well it depends on what the CF rule is !
If it's a colour change of the cell or Font, then a code can do that, but we would need to know Exactly what that colour is ?
Very hard to control people that fiddle.....apart from locking worksheets, then running the code with an unprotect built into that code

You're right, hard to stop people fiddling. The exact reason why I wrote in a code to make a backup file in a separate location each time someone hits the "Save Files" button macro.

The conditional format rule I have applied is =COUNTIF('Valid List'!$A$1:$A$140,B2)=0.
The colour I chose for the formatting was the standard dark red in the colour pallet (sorry if that doesn't help), with white text.

 

[Michael M]

Assuming you mean VBRed, try
This will ID any cells that have changed to red
Here is the colour palette, which may help with the numbering

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = 300 'change to suit
For r = x To lr
    If Cells(r, 2).Interior.ColorIndex = 3 Then ' change colour number to suit
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

 

[Truculent Llama]

Assuming you mean VBRed, try
This will ID any cells that have changed to red
Here is the colour palette, which may help with the numbering

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = 300 'change to suit
For r = x To lr
    If Cells(r, 2).Interior.ColorIndex = 3 Then ' change colour number to suit
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

It does not seem to be working.
I've used this Function:

Function GetColor(r As Range) As Integer
GetColor = r.Interior.ColorIndex
End Function

to try and find out what the colour is, but all my cells are returning the same value: -4142

 

[Michael M]

Have you tried changing to the standard red to start with ??

 

[Truculent Llama]

Have you tried changing to the standard red to start with ??

I've tried a few colours.
I've tested my function with a blank sheet and the function is (now) working as expected. However it's not returning any value other than -4142 for conditional formatting. If I manually colour it, it returns a value.

 

[Truculent Llama]

Have you tried changing to the standard red to start with ??

OK, it saddens me how long this took, but I've found an answer online.
All I needed to do was add .DisplayFormat into the code.

Sub MM1()
Dim lr As Long, n As Integer, x As Long, r As Long: lr = Cells(Rows.Count, "B").End(xlUp).Row
n = 0
x = 300 'change to suit
For r = x To lr
    If Cells(r, 2).DisplayFormat.Interior.ColorIndex = 3 Then ' change colour number to suit
      n = n + 1
    End If
  Next r
  If n > 0 Then MsgBox ("There are Errors in the last " & lr - (lr - n) & "rows !")
End Sub

I'll need to do some further testing to make sure this fits with the rest of my macros, but I don't see why not.
Going home now so I'll do this tomorrow.

 

[Truculent Llama]

I'll need to do some further testing to make sure this fits with the rest of my macros, but I don't see why not.
Going home now so I'll do this tomorrow.

Seems to work exactly as I'd hoped.
Michael, you've not only helped me achieve my goal but taught me a few things along the way too. Thank you so much for your help

 

[Michael M]

Glad you got a positive outcome ??