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

Ian,

This isn't exactly what you've asked for, but I'm not certain exactly what
you've asked for.

If the data is contiguous (touching), select a cell that isn't empty, Edit -
Go to - Special - Used range.

I suspect anything else will require a macro.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"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

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