VBA to highlight sequential numbers

[nfunkhouser]

I have a spreadsheet that we have service providers send in. They regularly auto fill columns like the phone number, but they don't realize it fills as a series then we end up with bad data on our end.

We run a macro to highlight missing and invalid data before we do anything with them. Is there a way to highlight sequential numbers in these columns? It would need to be by column header, not letters, because depending on the type of services they provide, we have send spreadsheets with different information we need...so they're not always in the same column. We can't maintain a separate macro for each type of service, so if it could look for the header name - like "phone number" and highlight down that way, that would be ideal.

 

[Joe4]

Are the phone numbers entered as text, or are they entered as numbers with special phone number formatting?

If you are unsure, you could enter this formula in some blank cell and see what it returns.
If your first phone number entry is in cell BA2, use this formula:

=ISNUMBER(BA2)

 

[nfunkhouser]

Are the phone numbers entered as text, or are they entered as numbers with special phone number formatting?

If you are unsure, you could enter this formula in some blank cell and see what it returns.
If your first phone number entry is in cell BA2, use this formula:

=ISNUMBER(BA2)

They are numbers that are formatted with the special characters to look like phone numbers. Within the existing macro, we remove all special characters in hopes of just ending up with a field of numbers. I can't say that no one ever changes it to a text field, because people like to mess with things, but typically it's stored as a number.

 

[Joe4]

OK, if they are just numbers, then what you want can done pretty easily with Conditional Formatting (no VBA required).
Or if you really want it in VBA, you can use VBA to set-up the Conditional Formatting (you can use the Macro Recorder and record yourself setting up the Conditional Formatting manually in order to get most of the VBA code you need to do this).

So, here is my example:

What I did was select cells BA2:BA10, go to Conditional Formatting, and enter this Conditional Formatting formula:

=BA2=BA1+1

and then select the yellow highlighting option.

 

[nfunkhouser]

OK, if they are just numbers, then what you want can done pretty easily with Conditional Formatting (no VBA required).
Or if you really want it in VBA, you can use VBA to set-up the Conditional Formatting (you can use the Macro Recorder and record yourself setting up the Conditional Formatting manually in order to get most of the VBA code you need to do this).

So, here is my example:
View attachment 123939

What I did was select cells BA2:BA10, go to Conditional Formatting, and enter this Conditional Formatting formula:

=BA2=BA1+1

and then select the yellow highlighting option.

 

[nfunkhouser]

Thank you. Unfortunately, as I mentioned originally - we have multiple spreadsheet formats that go out depending on the services provided - so phone number isn't always in the BA column. I was hoping that by using VBA, I could use a code that would complete the task by finding the column header and not using column letters.

 

[Joe4]

You can - and then you can make that dynamic in VBA and use VBA to create that Conditional Formatting.