WORKING WITH COLORINDEX AND MULTIPLE WORKBOOKS

[sylink]

Am working with 2 workbooks: book1.sheet1 and book2.sheet1. The task is
to search thru the usedrange in book1.sheet1 for cell with interior
colorIndex of 43 and apply to corresponding cell in book2.sheet2;
That means with cell of colorInedx 43 found, then identify and select
the corresponding value in the Column A. Then with that value turn to
Book2.sheet1 and Search thru column A for same value. Identify the cell
that corresponds to the one in book2 with color index 43 and apply
color index 56 to the cell.

both sheets contains same set data structure i.e same columns.
book1.sheet1 has more rows. Columns A arrangements are irregular.
Usedranges are not fixed. see illustrations below:

The cells marked X has interior.colorindex of 43

Book1.sheet1
COLUMN A COLUMN B COLUMN C COLUMN D

Greg x
x
John x
James x
Peter
Joy
x



Book2.sheet1
COLUMN A COLUMN B COLUMN C COLUMN D

Greg

John
James

 

[Nigel]

Simple way to do it - run the code from book2. Adapt as required.

Sub CopyToBook1()
Dim xr As Long, xlr As Long, xValue As String
Dim xr2 As Long, xlr2 As Long
With Sheets("Sheet1")
xlr = .Cells(.Rows.Count, 1).End(xlUp).Row
For xr = 1 To xlr
If Len(Trim(.Cells(xr, 1))) > 0 And .Cells(xr, 1).Interior.ColorIndex
= 43 Then
xValue = Trim(.Cells(xr, 1))
' go find it in book1
With Workbooks("Book1").Sheets("Sheet1")
xlr2 = .Cells(.Rows.Count, 1).End(xlUp).Row
For xr2 = 1 To xlr2
If Trim(.Cells(xr2, 1)) = xValue Then
.Cells(xr2, 1).Interior.ColorIndex = 56
Exit For
End If
Next xr2
End With
End If
Next xr
End With
End Sub