Highlight first blank cell in column
 

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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:D" & lngLastRow)
If IsEmpty(rng) Then
rng.Interior.ColorIndex = 6
Exit For
End If
Next rng

End Sub
--
Cheers,
Ryan


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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:D" & lngLastRow)
If IsEmpty(rng) Then
rng.Select
Exit For
End If
Next rng

End Sub
--
Cheers,
Ryan


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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 (MVP - Excel)


"Ryan H" wrote in message
...
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:D" & lngLastRow)
If IsEmpty(rng) Then
rng.Select
Exit For
End If
Next rng

End Sub
--
Cheers,
Ryan


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
You can avoid looping if D13 is a blank cell (no formula within).

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

Try

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

--
Jacob


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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).

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Try

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

--
Jacob


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

Oops..I didnt notice your response to my post..Try your code with Range D1:D5
blank...

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

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

--
Jacob


"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Try

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

--
Jacob


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.


.

Highlight first blank cell in column
 

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.
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Ryan H" wrote in message
...
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:D" & lngLastRow)
If IsEmpty(rng) Then
rng.Select
Exit For
End If
Next rng

End Sub
--
Cheers,
Ryan


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.


.

Highlight first blank cell in column
 

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).Sel ect

or

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Oops..I didnt notice your response to my post..Try your code with Range
D1:D5
blank...

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

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

--
Jacob


"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
Try

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

--
Jacob


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.


.

Highlight first blank cell in column
 

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).Int erior.ColorIndex=3

or

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
You can avoid looping if D13 is a blank cell (no formula within).

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.

Highlight first blank cell in column
 

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).Sel ect

or

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

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
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.
--
Cheers,
Ryan


"Rick Rothstein" wrote:

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 (MVP - Excel)


"Ryan H" wrote in message
...
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:D" & lngLastRow)
If IsEmpty(rng) Then
rng.Select
Exit For
End If
Next rng

End Sub
--
Cheers,
Ryan


"gotroots" wrote:

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.



"Jacob Skaria" wrote:

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

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

--
Jacob


"gotroots" wrote:

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

Example

D1:D12 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.


.