Vba code to color cell if value has specific formatting and lenght (sos pls help a noob)

Good morning,

I'm a trainee at a recently new job, and started to study VBA as a solution for my work. I managed to create some simple coding for some tasks, but this seems too complex for me

I have a database that I must analyse the accuracy of the data we collect, and I think I can gain lots of time if I manage to highlight with RGB color the phone entries, based on the lenght and the entry formatting

For example, very often, cellphone numbers (which contains 1 extra number "9" in front of the number set) are inserted in the residential number field, therefore I could identify them simply looking this extra number:

Residential e.g: (11)4557-9086
Mobile e.g: (11)95667-3567

The problem is that I also make a parallel analysis, wether the number has it's local code or not ("(11)"), and considering the mobile numbers, I would collor the cell as a "wrong field" entry, hierarchically, although it's also a "no local code" entry.

So for instance:

1. (11)97554-6899 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number
2. (11)98644-5482 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number
3. (11)4556-3287 ---> RGB(198, 239, 206) - Correct Field, as it has the local code and has no "9" sufix and therefore is a residential number
4. 4312-8990 ---> RGB(255, 235, 156) - No Local Code, as it is a residential number for not having the "9" sufix, but it does not carry the local code
5. 95567-1234 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number (although it also does not carry the local code, hierarchically, I will ignore this fact)
6. 94879-0045 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number (although it also does not carry the local code, hierarchically, I will ignore this fact)
7. (11)2345-9878 ---> RGB(198, 239, 206) - Correct Field, as it has the local code and has no "9" sufix and therefore is a residential number
8. 5678-9834 ---> RGB(255, 235, 156) - No Local Code, as it is a residential number for not having the "9" sufix, but it does not carry the local code
9. 94456-1264 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number (although it also does not carry the local code, hierarchically, I will ignore this fact)
10. (11)96789-0678 ---> RGB(242, 242, 242) - Wrong Field, as it has a "9" sufix after the local code and cannot be a residential number

As for 2 last rules, all mobile numbers obligatorily contain a "9" sufix, and it cannot be any other number, and there's a minimum/maximum total of numbers, so if the macro comes across something like:

1. (11)79560-4567 ---> RGB(255, 199, 206) - Error Entry, as the sufix in this case is "7" and not "9" (hierarchically, I will considerer errors above Wrong Field entries, as this entry is also supposed to be in the mobile field)
2. (11)45567-9844 ---> RGB(255, 199, 206) - Error Entry, as the sufix in this case is "7" and not "9" (hierarchically, I will considerer errors above Wrong Field entries, as this entry is also supposed to be in the mobile field)
3. 8554560-2366 ---> RGB(255, 199, 206) - Error Entry, as the number is too BIG to be either a residential or a mobile number
4. 233448-5295 ---> RGB(255, 199, 206) - Error Entry, as the number is too BIG to be either a residential or a mobile number
5. 156-3456 ---> RGB(255, 199, 206) - Error Entry, as the number is too SHORT to be either a residential or a mobile number


The action should only change the cell interior collor, there's no need for replacing the data inside it. If some excel wizard and beloved human could help me on this I'd be forever thankful. I have to go through a list of 150000 registries and been doing that using filter and sorting (it feels like dying), this would save me weeks, even months.


I have tried creating some codes, merging things I find in the internet, but it just too complex for my actual knowledge as it carries hierarchy and stuff, so I'm sorry for not giving a start kick or something

PS: The data is set on column M

Originally Posted by jindon

Try the attached.

Please Login or Register  to view this content.

Hi, thanks for the reply!

The button seems to work, but when I try to use the code on my dataset, changing the column letter b to m, I get a Run Time Error '5': Invalid Procedure Call or Argument

Something tells me the code gives me the option between the button and to run manually. For the record, I don't plan using it as a button, in case it's important to know

Sorry

I changed the b4 and b to m2 and m, on the With Range line

Please Login or Register  to view this content.

I wasn't quite sure how to attach in a reply, so I edited the post

Because the data is from the company and sensitive, I deleted everything else and kept some values in the column that I need to analyse

I get a Run Time Error '5': Invalid Procedure Call or Argument

I'm not sure if it's the correct way, but sometimes I run a macro directly in the sheet code, without creating modules, as I need it to work just once. Could be anything related to that?

What you mean by standard?

Hi Kenneth, thanks for the reply

I tried the code, and either nothing happens or it reseted the interior color (as the title suggests)

Jindon,

I tried to add it as a module, and it gives me the same error but with a sightly different box message. When I click to debug it highlights the line identified below

Please Login or Register  to view this content.

Here's the attachment

Sorry, updated the same file now with the module

I am! What steps do you take to make it run? I simply click on the "play" icon as I try to make it run without a button. If I position the mouse over the Optional Explicit, it pop ups the macro assign window, I guess for assigning it to a button.

What am I doing wrong? I must use it as a button instead of trying to run the code directly?

Update: Even trying to assign to a button it gives me the same error and open ups the VBA window to point out the error line (the one I highlighted earlier)

Update 2: The only way it worked is on the sheet you first shared, but only using the button, and although it works, when I click it it gives me the same error message and if I click on "END" then the columns cells are indeed colored

I get a different error this time: Invalid procedure call or argument

It actually worked when I clicked "End" and not debug

I changed this line to RGB so I get the same as the "Calculation" cell style template

Please Login or Register  to view this content.

, the problem is I get a different color interior as RGB, (255 255 255) instead of (242 242 242)

Hey Jindon, I found something about the error code:

Please Login or Register  to view this content.

Maybe it's something about the version? I also noticed it works partially. Whenever I click on "End" instead of debug it seems to work, but as I look into the data there are cells that fit into the conditions that aren't colored.

I also noticed excel gets really laggy after using the code, even after the run has finished. If I click on the filter arrow excel freezes and takes forever to open the filter window

I'm on windows 10.

I just figured the cells that are not colored it's probably because of the different local code, for example:

(12)99721-2676
(12)99779-1448
(13)97408-7223
(13)98159-9839
(17)3521-7646

and also for longer numbers like this:

(11)9952949677

I don't mind the error as it seems to work. Any idea how to include other local codes? Maybe using the "()" as reference?

Try replace the function with

Please Login or Register  to view this content.

It worked for codes from 11 to 19. If we can make it work up to code 99 then it suits me perfectly!!

Function in post #33 has been updated to allow 11 to 99.

I noticed some kind of a bug. The code does not actually work. It does change the cell colors when I chose to End the run instead of debugging. The sheet gets real laggy when I try to filter the result, then I wait a few moment and manage to sort and filter. Then when I decide to save the project as macrofree and open it up again, all cells that were colored return to no fill.