VideoPhoto

02 January, 2022

How to design colorful cell contents in Excel

If you need to design a colorful title or banner or something similar, so that every character or group of characters or digits has a distinct color, then you can find this post helpful. Here are just two simple examples of effects you can achieve:

You can easily produce such effects quickly by entering your text (including also digits) in any cell of your worksheet, selecting the cell, and running the macro listed below. Just remember that if your cell contains just a number it must be formatted as text for this purpose.

The macro can be entered/copied into any VBA module of your workbook. Obviously, it can be modified as needed for your specific needs. Enjoy!

Sub clrFonts()
'Colors every character within a string of selected cell
Dim cnt As String
Dim rng As Range
Dim n As Long
Set rng = ActiveCell
For n = 1 To Len(rng.Value)
    cnt = Mid(rng.Value, n, 1)
    If cnt = "a" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "b" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "c" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "d" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "e" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 0): GoTo cont 'brownish
    If cnt = "f" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 255): GoTo cont 'd red
    If cnt = "g" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'green blue
    If cnt = "h" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 0): GoTo cont 'brown
    If cnt = "i" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 0): GoTo cont 'vd green
    If cnt = "j" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'd blue
    If cnt = "k" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'd grey
    If cnt = "l" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'vd brown
    If cnt = "m" Then rng.Characters(n, 1).Font.Color = RGB(192, 192, 192): GoTo cont 'l grey
    If cnt = "n" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 128): GoTo cont 'l green
    If cnt = "o" Then rng.Characters(n, 1).Font.Color = RGB(153, 153, 255): GoTo cont 'l blue
    If cnt = "p" Then rng.Characters(n, 1).Font.Color = RGB(153, 51, 102): GoTo cont 'vvd brown
    If cnt = "q" Then rng.Characters(n, 1).Font.Color = RGB(255, 255, 204): GoTo cont 'vl yellow
    If cnt = "r" Then rng.Characters(n, 1).Font.Color = RGB(51, 153, 102): GoTo cont 'green blue
    If cnt = "s" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 102): GoTo cont 'vvvd brown
    If cnt = "t" Then rng.Characters(n, 1).Font.Color = RGB(255, 128, 128): GoTo cont 'd orange
    If cnt = "u" Then rng.Characters(n, 1).Font.Color = RGB(0, 102, 204): GoTo cont 'vdd green
    If cnt = "v" Then rng.Characters(n, 1).Font.Color = RGB(204, 204, 255): GoTo cont 'dd grey
    If cnt = "w" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 128): GoTo cont 'vvd blue
    If cnt = "x" Then rng.Characters(n, 1).Font.Color = RGB(204, 153, 255): GoTo cont 'violet
    If cnt = "y" Then rng.Characters(n, 1).Font.Color = RGB(51, 102, 255): GoTo cont 'md blue
    If cnt = "z" Then rng.Characters(n, 1).Font.Color = RGB(102, 102, 153): GoTo cont 'greenish
    If cnt = "0" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 0): GoTo cont 'black
    If cnt = "1" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 0): GoTo cont 'green
    If cnt = "2" Then rng.Characters(n, 1).Font.Color = RGB(255, 0, 0): GoTo cont 'red
    If cnt = "3" Then rng.Characters(n, 1).Font.Color = RGB(0, 0, 255): GoTo cont 'blue
    If cnt = "4" Then rng.Characters(n, 1).Font.Color = RGB(0, 255, 255): GoTo cont 'l blue
    If cnt = "5" Then rng.Characters(n, 1).Font.Color = RGB(102, 0, 150): GoTo cont '???
    If cnt = "6" Then rng.Characters(n, 1).Font.Color = RGB(128, 128, 0): GoTo cont 'greenish
    If cnt = "7" Then rng.Characters(n, 1).Font.Color = RGB(128, 0, 128): GoTo cont 'd brown
    If cnt = "8" Then rng.Characters(n, 1).Font.Color = RGB(0, 128, 128): GoTo cont 'd green
    If cnt = "9" Then rng.Characters(n, 1).Font.Color = RGB(255, 153, 204): GoTo cont 'rouge
    If cnt Like "[A-Z]" Then
        rng.Characters(n, 1).Font.Color = RGB(255, 0, 255)  'dark red
    ElseIf cnt <> " " Then
        rng.Characters(n, 1).Font.Color = RGB(0, 0, 0)  'black
    End If
cont:
Next n
End Sub

 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.