Assume each sheet has a header row in row 1

sub AAA()
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell as Range, swop as String
with worksheets("Carriage")
set rng1 = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
end with
With worksheets("Customer")
set rng2 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
rng1.Interior.colorIndex = xlNone
for each cell in rng1
swop = cell.value
set rng3 = rng2.Find(What:=swop, After:=rng2(1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if not rng3 is nothing then
rng3.offset(0,7).Value = cell.offset(0,1).Value
else
cell.Interior.ColorIndex = 3
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"fugfug" <fugfug.1tad2i_1123236386.0787@excelforum-nospam.com> wrote in
message news:fugfug.1tad2i_1123236386.0787@excelforum-nospam.com...
>
> I have two sheets, one with a customer name and corresponding carriage
> cost (named carriage) and one with customer name and many other columns
> of information including a blank space for carriage cost (named
> customer.) The customer names are in different orders on the two
> different sheets.
>
> I have wrote the below code which copies the carriage cost from sheet
> carriage into sheet customer which works until a customer that is on
> the carriage sheet is not on the customer sheet. I would like the macro
> to highlight the customer on the carriage sheet that is not on the
> customer sheet and then continue with the macro.
>
> Any ideas? Thank you for your help. Code below.
>
> Sub carriagemove()
> '
> ' carriagemove Macro
> ' Macro recorded 14/07/2005 by James Fuggle
> '
>
> '
> Dim swop As String
> Dim rw As String
>
> Sheets("Carriage").Select
> Range("A300").Select
> ActiveCell.FormulaR1C1 = "Grand total"
> Range("A1").Select
> Cells.Find(What:="Grand total", After:=ActiveCell,
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False).Activate
> Application.CutCopyMode = False
>
> rw = ActiveCell.Row
> Range("A1").Select
>
> Do While ActiveCell.Row < rw
> Sheets("Carriage").Select
>
> ActiveCell.Offset(1, 0).Select
>
> swop = ActiveCell
>
> ActiveCell.Offset(0, 1).Select
> Selection.Copy
>
> Sheets("Customers").Select
>
> Cells.Find(What:=swop, After:=ActiveCell, LookIn:= _
> xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
> SearchDirection:= _
> xlNext, MatchCase:=False).Activate
> Application.CutCopyMode = False
>
> If ActiveCell.Row >= rw Then Exit Do
>
> ActiveCell.Offset(0, 7).Select
>
> Sheets("Carriage").Select
> Selection.Copy
>
> Sheets("Customers").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> False, Transpose:=False
>
> Sheets("Carriage").Select
> ActiveCell.Offset(0, -1).Select
>
> Loop
>
> End Sub
>
>
> --
> fugfug
> ------------------------------------------------------------------------
> fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
> View this thread: http://www.excelforum.com/showthread...hreadid=393234
>