No Subject

[CR]

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

 

[Dave Peterson]

If the cell in row 2 of the column has a colorindex of 16, then it's used.
Otherwise, the cell in row 4 is used. (No checking at all!):

Option Explicit
Sub testme()
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim WhichCellToUse As Range
Dim WhichColorIndex As Long

WhichColorIndex = 16

With Worksheets("Somesheetnamehere")
FirstCol = .Range("d1").Column
LastCol = .Range("s1").Column
FirstRow = 6
LastRow = 64

For iCol = FirstCol To LastCol
'it's either row 2 or row 4, right?
'not both and not neither, right??
If .Cells(2, iCol).Interior.ColorIndex = WhichColorIndex Then
Set WhichCellToUse = .Cells(2, iCol)
Else
Set WhichCellToUse = .Cells(4, iCol)
End If
For iRow = FirstRow To LastRow
If LCase(.Cells(iRow, iCol).Value) _
= LCase(WhichCellToUse.Value) Then
.Cells(iRow, iCol).Interior.ColorIndex = WhichColorIndex
End If
Next iRow
Next iCol
End With

End Sub

(untested, but it did compile.)