Find first and last cells in column that have a certain fill color
 

Hello All,

I am looking to create a macro that looks at a column, finds the first cell
in that column that has a green (colorindex=35) fill color and names it
(named range). Then I would like the macro to find the last cell in the
column that has that same color fill and name that one as well. Then I would
like to be able to reference the range that falls between the two named
cells and have the macro perform text to columns on it. Does anyone have any
ideas on how I might achieve this? Thanks in advance,

Ashleigh

Find first and last cells in column that have a certain fill color
 

Ashleigh,

Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim BigRng As Range

With Worksheets("Sheet1")
For Each Rng In Application.Intersect(.Range("A:A"),
..UsedRange)
If Rng.Interior.ColorIndex = 35 Then
If Rng1 Is Nothing Then
Set Rng1 = Rng
Else
Set Rng2 = Rng
End If
End If
Next Rng
End With

Set BigRng = Range(Rng1, Rng2)
ThisWorkbook.Names.Add "TheRange", BigRng
Range("TheRange").Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Ashleigh K." wrote in message
...
Hello All,

I am looking to create a macro that looks at a column, finds
the first cell
in that column that has a green (colorindex=35) fill color and
names it
(named range). Then I would like the macro to find the last
cell in the
column that has that same color fill and name that one as well.
Then I would
like to be able to reference the range that falls between the
two named
cells and have the macro perform text to columns on it. Does
anyone have any
ideas on how I might achieve this? Thanks in advance,

Ashleigh