Hi Norman,

Thank you very much for responding.
Your Macro works very well, but it highlights every unused cell in the
entire range.
I am looking to Start highlighting at the last used row and go up to
the last used cell
in each column (which is different in each column).

So there would be nothing below the last used row and when the shading
is going up,
it will stop when it hits a used cell

Make sense??
Dave

Norman Jones Wrote:
Hi Dave,

Try putting the following in a normal module in the workbook in
question:

Sub Initialize()

Dim rng As Range

Range("MyNamedRange").Cells.Interior.ColorIndex _
= xlNone

On Error Resume Next
Set rng = Range("MyNamedRange").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.Interior.ColorIndex = 36
End If

End Sub

Then paste the following into the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Dim rCell As Range

Set rng1 = Range("MyNamedRange")
Set rng2 = Intersect(Target, rng1)

If Not rng2 Is Nothing Then
For Each rCell In rng2
If IsEmpty(rCell) Then
rCell.Interior.ColorIndex = 36
Else
rCell.Interior.ColorIndex = xlNone
End If
Next
End If
End Sub


---
Regards,
Norman



"Piranha" wrote
in
message ...

Hi,
Range - "MyNamedRange"
Rows with data and blank cells

Goal:

From last used row in range, (with data in row),
going up, shade cells in columns, only untill a cell with data
Would look kinda like a bar graph showing how long, rows have not
had
data.
A B C D E
h, r, , f, a
i, y, , , b
p, , l, , g
t, , , , t

So
B3,B4
C4
D2,D3,D4
Would be shaded untill new data was added

Now don't laugh but this is what i am working on (no it dont work)
:)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "MyNamedRange"
Cells.Borders().LineStyle = xlNone
Cells.Interior.ColorIndex = 0
LastRow = .Cells(.Rows.Count, "A:AP").End(xlUp).Row
Interior.ColorIndex = 36
End If
End Sub


Thx
Dave


--
Piranha

------------------------------------------------------------------------
Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=20435
View this thread:
http://www.excelforum.com/showthread...hreadid=383760



--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=383760