How to hide colored text from excel using vba macr

Question

How to hide text from excel using vba macro?

Answer 1

Hide complete rows or columns

You can hide complete rows or columns e. g. by this:

Worksheet(...).Range(...).EntireRow.Hidden = True
Worksheet(...).Range(...).EntireColumn.Hidden = True

You can also group rows or columns and set the outline level to hide them:

Worksheet(...).Range(...).EntireRow.Group
Worksheet(...).Outline.ShowLevels RowLevels:=1

A single cell can not be hidden (it could only be deleted, but that would move the other cells beneath it).

Hide parts of a cell

Unfortunately you can not hide some characters within a cell like in Microsoft Word by Font.Hidden.
But by following code you can change all blue characters to very small white ones.

As there are many different "blue" colors (not only RGB(0,0,255)), my code checks, if the blue part of the color is higher than the red and green part.

Private Sub ChangeBlueTextToWhiteText()
    Dim ws As Worksheet
    Dim i As Integer
    Dim c As Range
    Dim currentColor As Long
    Dim r As Byte, g As Byte, b As Byte

    Set ws = ActiveSheet
    For Each c In ws.UsedRange.Cells
        If c.Characters.Count > 0 Then
            For i = 1 To c.Characters.Count

                currentColor = c.Characters(i, 1).Font.Color
                If currentColor < 0 Then currentColor = &H1000000 - currentColor
                r = currentColor Mod 256             ' red part
                g = currentColor \ 256 Mod 256       ' green part
                b = currentColor \ (2 ^ 16) Mod 256  ' blue part

                If b > r And b > g Then ' if blue part is largest, then it's kinda "blue"
                    c.Characters(i, 1).Font.Color = vbWhite
                End If
            Next i
        End If
    Next c
    Set ws = Nothing
End Sub

If you want to have the white characters as narrow as possible, you may additionally chose a narrow font and a small font size like this:

c.Characters(i, 1).Font.Name = "Arial Narrow"
c.Characters(i, 1).Font.Size = 1
c.Characters(i, 1).Font.Subscript = True