Pass condition formatting across worksheets via vba.

[Living the Dream]

Hi guy's

Currently, I have this working very well.

....................................................
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 5 Then Exit Sub

On Error GoTo ErrHandler
Application.EnableEvents = False

If Target.Column = 11 Then
If IsNumeric(Target.Value) Then
With Target
.Offset(, -10).Resize(, 14).Interior.ColorIndex = 6
End With
End If
If Target.Value = "" Then
With Target
.Offset(, -10).Resize(, 14).Interior.ColorIndex = 2
End With
End If
If Target.Column = 12 Then
If IsNumeric(Target.Value) Then
With Target
.Offset(, -11).Resize(, 14).Interior.ColorIndex = 6
End With
End If
If Target.Value = "" Then
With Target
.Offset(, -11).Resize(, 14).Interior.ColorIndex = 2
End With
End If
End If
End If

ErrHandler:

Application.EnableEvents = True

End Sub
....................................................

What I have been asked to do is expand each of the major IF's and branch it across 2 other worksheets beyond this sheet("Inbound")and by I mean the following:

The 1st Part:
in the first Target.column which = 11, I need to .Offset(,9).value and use that value to cycle through 2 others, Firstly:

sheets("Outbound").Columns("E:E")= find.("Inbound").Target.Offset(,9).Value and change the interior color of Column("E").offset(,-4).Resize(,13).Interior.ColorIndex to match.

The 2nd Part:
For this part we have to look at a second matching criteria to locate the Target.Value.

Using our established Sheets("Outbound").Column.Value match in Column("E") we need to obtain our second criteria value, so it would be:

Sheets("Outbound").Columns("E").value And Columns("E").Offset(,8).value

So the final part of this rather complicated looking code will be

Sheets("" & Columns("E").Offset(,8).value).Activate
Columns("G:G").Target.Value.Offset(,-6).resize(,12).Interior.ColorIndex to match.

So this long-winded mess should look something like this in the 1st If section:

If Target.Column = 11 Then
If IsNumeric(Target.Value) Then
With Target
..Offset(, -10).Resize(, 14).Interior.ColorIndex = 6
End With
sheets("Outbound").Columns("E:E")= find.("Inbound").Target.Offset(,9).Value
If Find = True Then
Column("E").offset(,-4).Resize(,13).Interior.ColorIndex to match

Sheets("" & Columns("E").Offset(,8).value).Activate
Columns("G:G").Target.Value.Offset(,-6).resize(,12).Interior.ColorIndex to match.
End If

I truly hope someone can make sense of what it is I, or should I say somebody else actually needs.

A Massive TIA

Cheers
Mick