Hopefully this will be the last help I need on this little project.

I have a range D3:S64

In each column, in both cells 2 and 4 is a city name.
In cells 6 through 64 are also city names with some blank cells. (Could be
any of the cells in the column Range that are blank)

One of the cells, either 2 or 4 has an Interior.ColorIndex = 16

I need to loop through each column using the Color.Index to identify the
text value to look for
and then search the column and find the cells that have the same text value
as the cell with the Color.Index

So:

If Cell B2 has a Color.Index=16 and says Atlanta , I need to find all of the
cells "B6:B64"
That also have Atlanta and make those cells Color.Index=16

Then next column.

I've put about four hours into it and still have had no luck trying to do
something
that some of you can probably do in five minutes. (or less :~)

Thanks'
CR

See if this works. I used interior.colorindex.


Sub ColorSheet()

StartCol = Range("D1").Column
endCol = Range("S1").Column

For ColCount = StartCol To endCol
'test if row 2 and 4 is colored
For HeaderRow = 2 To 4 Step 2
If Cells(HeaderRow, ColCount).Interior.ColorIndex < xlNone Then
Data = Cells(HeaderRow, ColCount).Value
DataColor = Cells(HeaderRow, ColCount).Interior.ColorIndex

Set DataRange = Range(Cells(6, ColCount), Cells(64, ColCount))
Set C = DataRange.Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddr = C.Address
Do
C.Interior.ColorIndex = DataColor
Set C = DataRange.FindNext(after:=C)
Loop While Not C Is Nothing And C.Address < FirstAddr
End If
End If
Next HeaderRow
Next ColCount


End Sub



"CR" wrote:



Hopefully this will be the last help I need on this little project.

I have a range D3:S64

In each column, in both cells 2 and 4 is a city name.
In cells 6 through 64 are also city names with some blank cells. (Could be
any of the cells in the column Range that are blank)

One of the cells, either 2 or 4 has an Interior.ColorIndex = 16

I need to loop through each column using the Color.Index to identify the
text value to look for
and then search the column and find the cells that have the same text value
as the cell with the Color.Index

So:

If Cell B2 has a Color.Index=16 and says Atlanta , I need to find all of the
cells "B6:B64"
That also have Atlanta and make those cells Color.Index=16

Then next column.

I've put about four hours into it and still have had no luck trying to do
something
that some of you can probably do in five minutes. (or less :~)

Thanks'
CR

Yes, It seems to work perfectly.

You guys are amazing.

Thanks' CR


"Joel" wrote in message
...
See if this works. I used interior.colorindex.


Sub ColorSheet()

StartCol = Range("D1").Column
endCol = Range("S1").Column

For ColCount = StartCol To endCol
'test if row 2 and 4 is colored
For HeaderRow = 2 To 4 Step 2
If Cells(HeaderRow, ColCount).Interior.ColorIndex < xlNone Then
Data = Cells(HeaderRow, ColCount).Value
DataColor = Cells(HeaderRow, ColCount).Interior.ColorIndex

Set DataRange = Range(Cells(6, ColCount), Cells(64, ColCount))
Set C = DataRange.Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddr = C.Address
Do
C.Interior.ColorIndex = DataColor
Set C = DataRange.FindNext(after:=C)
Loop While Not C Is Nothing And C.Address < FirstAddr
End If
End If
Next HeaderRow
Next ColCount


End Sub



"CR" wrote:



Hopefully this will be the last help I need on this little project.

I have a range D3:S64

In each column, in both cells 2 and 4 is a city name.
In cells 6 through 64 are also city names with some blank cells. (Could
be
any of the cells in the column Range that are blank)

One of the cells, either 2 or 4 has an Interior.ColorIndex = 16

I need to loop through each column using the Color.Index to identify the
text value to look for
and then search the column and find the cells that have the same text
value
as the cell with the Color.Index

So:

If Cell B2 has a Color.Index=16 and says Atlanta , I need to find all of
the
cells "B6:B64"
That also have Atlanta and make those cells Color.Index=16

Then next column.

I've put about four hours into it and still have had no luck trying to do
something
that some of you can probably do in five minutes. (or less :~)

Thanks'
CR