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

Find value in another column, change Interior.colorIndex
 

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

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

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

.