Highlighting only cells with data in them
 

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

Highlighting only cells with data in them
 

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

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