Highlight first blank cell in column

[gotroots]

What code do I need to allow the first blank cell found in a range to be
highlighted.

Example

D112 contains a value
D13 is the first blank cell in "D"

C5 is hightlighted

when code is run D13 is highlighted

Thank you if you can help with this.

 

[Ryan H]

This should help! This code will start at the top of Column D and scan down
to find the first empty cell and the highlight that cell yellow, then exit.
Let me know if I need to tweak it for you. Hope this helps! If so, let me
know, click "YES" below.

Sub Highlighter()

Dim lngLastRow As Long
Dim rng As Range

lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row

For Each rng In Range("D1" & lngLastRow)
If IsEmpty(rng) Then
rng.Interior.ColorIndex = 6
Exit For
End If
Next rng

End Sub

 

[Jacob Skaria]

You can avoid looping if D13 is a blank cell (no formula within).

Range("D1").End(xlDown).Offset(1).Interior.ColorIndex = 3

 

[gotroots]

Hi

Both solutions have performed there function, only the cell that is color
highlighted is not "click" hightlighted, if you can understand what I mean.
Like, if you were to select a cell with the mouse. Like an OnClick event.

 

[Ryan H]

So you just want to "Select" the cell? Use this,

Sub Highlighter()

Dim lngLastRow As Long
Dim rng As Range

lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row

For Each rng In Range("D1" & lngLastRow)
If IsEmpty(rng) Then
rng.Select
Exit For
End If
Next rng

End Sub

 

[Rick Rothstein]

No looping is necessary Ryan, this single line of code will do the same
thing...

Range("D1").End(xlDown).Offset(1 + 2 * (Range("D1") = "")).Select

 

[Rick Rothstein]

I think you would need to modify your code to this to protect against D1
being a blank cell...

Range("D1").End(xlDown).Offset(1+2*(Range("D1")="")).Interior.ColorIndex=3

 

[Jacob Skaria]

Try

Range("D1").End(xlDown).Offset(1).Select

 

[Rick Rothstein]

That will not work if D1 is the empty cell... take a look at the
modification of your statement that I posted Ryan (and also look at the
response I posted to your other message as well).

 

[Jacob Skaria]

Oops..I didnt notice your response to my post..Try your code with Range D15
blank...

You could try the below version to handle those scenarios...

Range("D" & IIF(Range("D1")="",1,Range("D113").Find ("").row)).Select

 

[Ryan H]

I should work on one liner coding. I'm falling in love with loops, which
could be dangerous at times assuming they take longer to execute.

 

[Rick Rothstein]

Good point back at me<g>, although I think the Row 13 reference in the OP's
original post was for example purposes only. Anyway, it looks like either of
these should always work correctly (unless the column is completely filled
with data)...

Columns("D").SpecialCells(xlCellTypeBlanks)(1).Select

or

Columns("D").Find("", After:=Cells(Rows.Count, "D")).Select

 

[Rick Rothstein]

In keeping with my response to your latest response to me in the other
sub-thread, it would be better to use either of these instead....

Columns("D").SpecialCells(xlCellTypeBlanks)(1).Interior.ColorIndex=3

or

Columns("D").Find("", After:=Cells(Rows.Count, "D")).Interior.ColorIndex=3

 

[Rick Rothstein]

Jacob found a possible problem area with the code I gave you, so here are
two different one-liners that should work correctly under all
circumstances...

Columns("D").SpecialCells(xlCellTypeBlanks)(1).Select

or

Columns("D").Find("", After:=Cells(Rows.Count, "D")).Select