Highlighting only cells with data in them
Hi Ian,
In your worksheet module (right-click your sheet tab | view code),try
pasting the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim Rng
Set Rng = Nothing
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlNone
With Me.UsedRange
If Application.CountA(.Cells) = 0 Then Exit Sub
On Error Resume Next
If Not .SpecialCells(xlCellTypeConstants, 23) Is Nothing Then
Set Rng = .SpecialCells(xlCellTypeConstants, 23)
On Error GoTo 0
End If
On Error Resume Next
If Not .SpecialCells(xlCellTypeFormulas, 23) Is Nothing Then
If Not Rng Is Nothing Then
Set Rng = Union(Rng, .SpecialCells(xlCellTypeFormulas, 23))
On Error GoTo 0
Else
Set Rng = .SpecialCells(xlCellTypeFormulas, 23)
End If
End If
End With
For Each cell In Rng
With cell.EntireRow.Interior
.ColorIndex = 6 '<--------------- Amend to suit
.Pattern = xlSolid
End With
With cell.EntireColumn.Interior
.ColorIndex = 6 '<--------------- Amend to suit
.Pattern = xlSolid
End With
Next cell
Application.ScreenUpdating = True
End Sub
---
Regards,
Norman
"Ian M" wrote in message
om...
In an Excel Spreadsheet, I wish to highlight all the rows and columns
which contain data, however these change every time I access the File.
Is there any way I can highlight ONLY those rows and columns which
contain data?
Any ideas?
Many thanks.
Ian M
|