VBA Express : Excel - Highlight cells where text is displayed

Excel

Highlight cells where text is displayed

Ease of Use

Intermediate

Version tested with

2000, 2003

Submitted by:

mdmackillop

Description:

All the text in a selected area will be highlighted by colouring the cells behind the text

Discussion:

Quickly formats the worksheet to highlight text without the need to select individual areas. This will also highlight all the text set in a particular column. Useful for identifying text where the columns are narrow. The user should adjust the code with regard to clearing previous colour application to suit his requirements. If assistance is required, ask at the forum. The function is designed only for left aligned cells

Code:

instructions for use

			Option Explicit 
 
Sub HighlightAll() 
    Dim rng As Range 
    Dim MyCol As Long 
     
     'Colour options
    If ActiveCell.Interior.ColorIndex <> xlNone Then 
        MyCol = ActiveCell.Interior.ColorIndex 
    Else 
        MyCol = 6 'Yellow
    End If 
     
     'Set range to highlight; All or Selection
    If Selection.Cells.Count > 1 Then 
        Set rng = Selection 
    Else 
        Set rng = ActiveSheet.UsedRange 
    End If 
     
     'Clear colour from cells
    If MsgBox("Highlight text?", vbYesNo) = vbNo Then 
        MyCol = xlNone 
    End If 
     
    TextHighlight rng, MyCol 
     
End Sub 
Sub HighlightColumn() 
    Dim rng As Range 
    Dim MyCol As Long 
     
     'Colour options
    If ActiveCell.Interior.ColorIndex <> xlNone Then 
        MyCol = ActiveCell.Interior.ColorIndex 
    Else 
        MyCol = 8 'Blue
    End If 
     
     'Set range to highlight
    Set rng = Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange) 
    If rng Is Nothing Then 
        MsgBox "Please select cell within UsedRange" 
        Exit Sub 
    End If 
     
     'Clear colour from selected column
    If MsgBox("Highlight text?", vbYesNo) = vbNo Then 
        MyCol = xlNone 
    End If 
     
    TextHighlight rng, MyCol 
     
End Sub 
 
Sub TextHighlight(rng As Range, MyCol As Long) 
    Dim cll As Range 
    Dim Cols As Long, i As Long 
    Dim MyWidth As Double, StWidth As Double, RunWidth As Double 
    Dim IndexCol As Long 
     'Tweak to suit; increase to extend
    Const AdjustRatio = 0.875 
     
    Application.ScreenUpdating = False 
    For Each cll In rng 
         'Option to omit formulas from highlighting
         'If Len(cll) > 0 And Left(cll.Formula, 1) <> "=" Then
        If Len(cll) > 0 Then 
            StWidth = cll.ColumnWidth 
            cll.Columns.AutoFit 
            MyWidth = cll.ColumnWidth 
            cll.ColumnWidth = StWidth 
            i = 0 
            Cols = 0 
            Do 
                If i > 0 And cll.Offset(0, i) <> "" Then Exit Do 
                RunWidth = cll.Offset(0, i).ColumnWidth 
                Cols = Cols + RunWidth 
                cll.Offset(0, i).Interior.ColorIndex = MyCol 
                i = i + 1 
            Loop Until Cols > MyWidth * AdjustRatio 
        End If 
    Next 
    Application.ScreenUpdating = True 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Save your work

Test the code:

Select a singlle cell or a selection
Press Alt+F8
Select HighlightAll and click Run
All the left aligned text in the sheet (or Selection) should be highlighted Yellow
By adding colour to the selection cell, this will be used in lieu of the default colour.
Select a cell in a column ontaining text
Press Alt+F8
Select HighlightColumn and click Run
All the left aligned text in the column of the Active Cell should be highlighted in Blue
By adding colour to the selection cell, this will be used in lieu of the default colour.
The code contains an option line to omit highlighting of formulas is preferred.

Sample File:

HighlightText.zip 11.72KB

Approved by mdmackillop

This entry has been viewed 173 times.