Highlight first 13 characters in a cell

[pasmith]

A friend of a friend created the below macro to highlight the first 13 characters in a cell in a different font color. Our system group policy disables macro's. I am able to run the two lines separately in VBA as a workaround. Is there a way to run both lines in VBA concurrently vice separately? Is there a way this can be done in Conditional Formatting? It would also be helpful if I could limit this highlight to 13 numerals as there are a few fields with leading characters. I already tried replacing the word “Characters” with the word “Numerals”. No joy. Thank you for any assistance.

Sub HRC_Highlight()
ActiveSheet.Range("F3:F5000").Characters(Start:=1, Length:=13).Font.Color = -320000
ActiveSheet.Range("P3:P5000").Characters(Start:=1, Length:=13).Font.Color = -16776961
End Sub

 

[Scott Huish]

I don't think it will be possible to do that in the cell without VBA, but if all 13 numbers are together in the cell, it could be possible to pull that information out with a formula into another cell, then you could just format the column in the color you want.

Can you provide a sample of the data?

 

[pasmith]

Yes I can supply a sample spreadsheet of the issue. I don't see a way to attach files. I can e-mail if you wish.

Below is the macro I have that works to format the first thirteen characters. What would be better is to only format numerals so verbiage such as "There are no possible faults." would remain in black font. Right now this worksheet is close to 4000 lines and will probably expand by 2-3,000 lines a year for the next few years. To extract this thirteen digit string into another column would only take up more of my limited time. Width wise, I'm already down to 70% normal size when I need to print. I can't afford another column. That would also increase workload. I keep a text box resident at the bottom of my spreadsheet so I can manually copy and paste the two lines into VBA. It works, but a Hot-key macro would be better and much more efficient. Our group policy will not allow macro's and my issue is no exception.

 

[Scott Huish]

Even if you are pasting those two lines into the immediate window in VBA, you are still running VBA, that in itself is in violation to the policy you have described. If your business needs this done and you can't do it with an extra column, then my recommendation would be to talk to your manager or boss to see if an exception can be made if this project is vital to your business.