VBA to change the text color only for numbers

[gberg]

Is there a way to have a macro only change the color of numbers for the selected cells?

51Alpha3 would turn into 51Alpha3

Thanks

 

[Trebor76]

Hi gberg,

Try this:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    For Each rngCell In Selection
        For i = 1 To Len(rngCell)
            rngCell.Characters(i, 1).Font.Color = IIf(IsNumeric(Mid(rngCell, i, 1)), vbRed, vbBlack)
        Next i
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[gberg]

Trebor,

That did the trick! Thanks for the help with this. One question; What do you need the "Option Explicit" before the start of the sub?

Thanks,

Greg

 

[Trebor76]

That did the trick! Thanks for the help with this.

Thanks for letting us know and you're welcome

One question; What do you need the "Option Explicit" before the start of the sub?

Option Explicit forces you to declare all variables as I have done with the two I used for your solution. Without it any undeclared variable(s) will be defined as variants which is the most expensive type of variable in terms of memory which can impact performance.
Hope that helps.

 

[gberg]

It does and thanks!

 

[Rick Rothstein]

Option Explicit forces you to declare all variables as I have done with the two I used for your solution. Without it any undeclared variable(s) will be defined as variants which is the most expensive type of variable in terms of memory which can impact performance.
Hope that helps.

There is a far better reason for using Option Explicit than variable declaration efficiency... it will save you from accidentally mistyping a variable name deep within your code. If you do not declare a variable as to its data type, then the first time you use a new variable anywhere within your code, VBA creates it then and there... declaring it as a variant. The major thing Option Explicit saves you from is that "then and there" creation. Here is an example. Assume you do not have Option Explicit activated...

MYVARIABLE = 100
MYVARIABLE = 5 * MYVAR1ABLE
Debug.Print MYVARIABLE

The intent of this code is to print 500, but it prints 0 instead. The problem is easy to see here because of the font this forum uses, but copy/paste that code into a procedure in a code window within the VBA editor and I bet you would have trouble seeing it. Now imagine that calculation was taking place in your code, but instead of printing the result to the Immediate Window, you went on to use the MYVARIABLE in more calculations. You would **think** that further calculation was using 500, not 0 and unless the final result from the procedure was so far off as to draw your attention, you would never know an error took place at all and, as a result, you would be basing any decisions from that code on an incorrect answer. Option Explicit saves you from this kind of mistyped variable name by forcing you to declare all variables and it makes VBA raise an error when it encountered an undeclared variable such as MYVAR1ABLE in the code above.

 

[Trebor76]

Thanks Rick. I will flag this thread as a go to for any future threads asking why using Option Explicit should be used.

Regards,

Robert