Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and show fill color | Excel Worksheet Functions | |||
Summing cells within a column based on cell fill color | New Users to Excel | |||
Find feature - Fill cells with color | Excel Discussion (Misc queries) | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel |