Validation and Highlighting & copying columns, using VBA

Truculent Llama

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Won't work if there are less than 300 rows .. :biggrin:
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.
 

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.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

excel color references

VBA 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).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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

excel color references

VBA 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).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:
VBA Code:
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

1616562593497.png
1616562645411.png
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Have you tried changing to the standard red to start with ??
 

Truculent Llama

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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

New Member
Joined
Mar 18, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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 (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,106
Messages
5,640,132
Members
417,126
Latest member
Jeffman52

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