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

  • Thread starter Thread starter Ashleigh K.
  • Start date Start date
A

Ashleigh K.

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
 
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
 
Back
Top