Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Subject
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Subject | Excel Worksheet Functions | |||
To, CC, BCC, Subject | Excel Discussion (Misc queries) | |||
No Subject | Charts and Charting in Excel | |||
No Subject | Excel Discussion (Misc queries) | |||
No Subject | Excel Discussion (Misc queries) |