Does anybody know of another way to do this?

"Jonathan Brown" wrote:

Barb,

This is very good. There is one problem I'm having with it. It's including
the active cell as part of the range as well, which is causing a circular
reference in my formula. I think this is because the active cell has no fill
as well. If I change the active cell to include a fill color and run it I
then get a "Run-time error '91': Object Variable or With block variable not
set" and it highlights the debug.print myNewRange.Address line.

How do I prevent it from including the active cell?

"JLGWhiz" wrote:

Hi Barb, Just a side note. If the cell color is set by conditional format,
this code fails.

"Barb Reinhardt" wrote:

I think I'd try this

Private Sub CommandButton1_Click()

Dim StartCell As Range
Dim myRange As Range
Dim myNewRange As Range

Set StartCell = ActiveCell
Set myRange = Nothing
Set myRange = StartCell.Offset(1 - StartCell.Row, 0).Resize(StartCell.Row, 1)
Debug.Print StartCell.Address, myRange.Address

Set myNewRange = Nothing

For i = myRange.Rows.Count To 1 Step -1
If myRange.Cells(i).Interior.ColorIndex = xlColorIndexNone Then
If myNewRange Is Nothing Then
Set myNewRange = myRange.Cells(i)
Else
Set myNewRange = Union(myRange.Cells(i), myNewRange)
End If
Else
Exit For
End If
Next i

Debug.Print myNewRange.Address

End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Jonathan Brown" wrote:

I need to select a range starting from the cell above the activecell and
continuing upward and stopping when it finds the first cell that contains a
fill color. In other words, I need to select the first group of cells above
the active cell that all have "no fill" as their fill color.

I then want to take that range and plug it into the =COUNTA() formula for
the activecell. But first I just want to find the range, I'll worry about
the formula later.

Here's what I've written so far which doesn't seem to be working:

Private Sub CommandButton1_Click()

Dim StartCell As Range

Set StartCell = ActiveCell

Do While ActiveCell.Interior.ColorIndex = xlColorIndexNone
Range(StartCell & ":" & ActiveCell.Offset(-1, 0)).Select
Loop

End Sub

Anybody have an idea on how to do this?