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.

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.

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.

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.

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.

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.

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.

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.

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.

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.


.

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.


.

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.


.

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.

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.


.