Auto-Highlight every other letter

[MrKowz]

I'm not sure if this is even possible, but is there a way I can write a macro that will put a highlight behind every other letter/number in a cell?

I was thinking using a for loop, telling it to highlight every letter/number that has an even number as its spot in the string... like for the following:

135792468

I want to highlight 3,7,2,6.

My reasoning for doing this is that I have about 400 strings of text ~200 characters in length that I need to pick apart to debug a piece of programming code written years ago. The code might take the first 4 characters, the next 7, the next 5, the next 3, and so on; I need a way to make this visually easier to work on.

Thank you

 

[Oaktree]

Try something like this:

Sub alt_color_chars()
Dim i As Integer, my_cell As Range
For Each my_cell In Range("A1:" & Range("A" & Rows.Count).End(xlUp).Address)
'convert numbers to text
my_cell.Formula = "'" & my_cell
'apply colors
For i = 1 To Len(my_cell)
If i Mod 2 = 0 Then my_cell.Characters(i, 1).Font.Color = vbRed
Next i
Next my_cell
End Sub

 

[MrKowz]

Awesome! Thanks for the VBA script. Gonna run it on the rest of my columns now.

Edit: Ran it on the rest of my cells and it put all of the numbers into scientific notation. Should I add something in there that changes the format so it shows all numbers?

 

[Oaktree]

I don't think you can have alternating font colors in a *number*, but you can in a text string.

That's why I included the line my_cell.Formula = "'" & my_cell, which would store numbers as text...

 

[MrKowz]

Right, and when it adds that in, it puts the number into a scientific notation.

For example:

14901490219960700 turns into 1.49014902199607E+16

 

[Oaktree]

Well, formatting the cells as text might help... but, if you have 17 digit numbers and they were not *imported* as text, you likely lost whatever the last two digits were (i.e. Excel would have made them 0's), so you might have to re-import them anyway.

Said differently, because Excel carries only 15 digits of precision, if you try to enter something like a 16 digit credit card number as a number (instead of as text), Excel will make the last number 0.

 

[MrKowz]

Ahh ok. Well thanks anyway. This was mainly to make things easier on my eyes, but I have been able to find long strings that are matching up with the code nicely. (Yay for doing things old-fashioned... with a highlighter!)