| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value in another column, change Interior.colorIndex
First post didn't work. I apologize if this is a duplicate.
Hello there, I wrote the following code to find a value in column B if the cell in columnS has an Interior.color.Index = 44, transfer the color to the cell with same value in B. (calcLastRow declared already) Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex = 44) DestChk2 = Trim(ActiveCell.Offset(0, 0).Value) Set SrcChk1 = .Find(What:=Trim(DestChk2), LookAt:=xlWhole, SearchOrder:=xlByColumns) On Error Resume Next For Each c In ColumnB If Not SrcChk1 Is Nothing Then If c.Offset(0, 0).Value = Trim(DestChk2) Then c.Offset(0, 2).Interior.ColorIndex = 44 c.Offset(1, 2).Interior.ColorIndex = 44 End If Err.Clear End If 'nothing Next ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 0)) End With I can't find where I'm making the mistake... is going thru column N Offset 0, 5 (Column S has some null values) but is not finding the value on Column B. Any help will be greatly appreciated! Thanks Gaba |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value in another column, change Interior.colorIndex
The Find method is a little bit tricky to use.
In your case, you could use CountIf instead. Like this: Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex = 44) DestChk2 = Trim(ActiveCell.Offset(0, 0).Value) ' Set SrcChk1 = .Find(What:=Trim(DestChk2), LookAt:=xlWhole, SearchOrder:=xlByColumns) n = Application.CountIf(ColumnB, _ Trim(CStr(DestChk2))) On Error Resume Next If Not n = 0 Then For Each c In ColumnB If Trim(CStr(c.Offset(0, 0).Value)) = Trim(DestChk2) Then c.Offset(0, 2).Interior.ColorIndex = 44 c.Offset(1, 2).Interior.ColorIndex = 44 End If Err.Clear Next End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 0)) End With Mishell "Gaba" a écrit dans le message de news: ... First post didn't work. I apologize if this is a duplicate. Hello there, I wrote the following code to find a value in column B if the cell in columnS has an Interior.color.Index = 44, transfer the color to the cell with same value in B. (calcLastRow declared already) Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex = 44) DestChk2 = Trim(ActiveCell.Offset(0, 0).Value) Set SrcChk1 = .Find(What:=Trim(DestChk2), LookAt:=xlWhole, SearchOrder:=xlByColumns) On Error Resume Next For Each c In ColumnB If Not SrcChk1 Is Nothing Then If c.Offset(0, 0).Value = Trim(DestChk2) Then c.Offset(0, 2).Interior.ColorIndex = 44 c.Offset(1, 2).Interior.ColorIndex = 44 End If Err.Clear End If 'nothing Next ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 0)) End With I can't find where I'm making the mistake... is going thru column N Offset 0, 5 (Column S has some null values) but is not finding the value on Column B. Any help will be greatly appreciated! Thanks Gaba |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value in another column, change Interior.colorIndex
Thanks so much Don. I've changed a little and it is working. Fewer lines,
doing the same job. Gaba "Don Guillett" wrote: How about, not tested, something simpler such as sub SAS() dim i as long for i = 1 to cells(rows.count,"b").end(xlup).row if cells(i,"s")=cells(i,"b") and cells(i,"s").interior.colorindex=44 then cells(i,"b").interior.colorindex=44 next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Gaba" wrote in message ... First post didn't work. I apologize if this is a duplicate. Hello there, I wrote the following code to find a value in column B if the cell in columnS has an Interior.color.Index = 44, transfer the color to the cell with same value in B. (calcLastRow declared already) Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex = 44) DestChk2 = Trim(ActiveCell.Offset(0, 0).Value) Set SrcChk1 = .Find(What:=Trim(DestChk2), LookAt:=xlWhole, SearchOrder:=xlByColumns) On Error Resume Next For Each c In ColumnB If Not SrcChk1 Is Nothing Then If c.Offset(0, 0).Value = Trim(DestChk2) Then c.Offset(0, 2).Interior.ColorIndex = 44 c.Offset(1, 2).Interior.ColorIndex = 44 End If Err.Clear End If 'nothing Next ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 0)) End With I can't find where I'm making the mistake... is going thru column N Offset 0, 5 (Column S has some null values) but is not finding the value on Column B. Any help will be greatly appreciated! Thanks Gaba . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Cell When Interior.ColorIndex =3 | Excel Programming | |||
| problem with interior.colorindex | Excel Programming | |||
| Use of Interior.ColorIndex | Excel Programming | |||
| Testing for Interior.ColorIndex | Excel Programming | |||
| Is ther a way to change the interior.colorindex of multiple rows | Excel Programming | |||
Linear Mode
