Auto-Highlight every other letter

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try something like this:

Code:
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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...
 
Upvote 0
Right, and when it adds that in, it puts the number into a scientific notation.

For example:

14901490219960700 turns into 1.49014902199607E+16
 
Upvote 0
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.
 
Upvote 0
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!) :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top