Worksheet_change question

[×לי]

Hi,

I am using the following code in order to color a cell in one worksheet if
the same value was typed in another worksheet.
it works fine when I am entering one value at the time and I am wondering if
it is possible to do the same if i will copy and paste several values at the
time. also it will be grate if i will be able to color not only the matching
cell but the range between A:T in the relevant row.

Thanks to Ron De Bruin for the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("Pouch log")
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As Variant
Dim lnLastRow As Long
Dim Rng As Range
Dim I As Long
lnLastRow = Cells(Rows.Count, "B").End(xlUp).Row
If Target.Address = Range("B" & lnLastRow).Address Then
MySearch = Array(Range("B" & lnLastRow))
myColor = Array("3")
lnLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
With ws.Range("A1:Z" & lnLastRow)
For I = LBound(MySearch) To UBound(MySearch)
Set Rng = .Find(What:=MySearch(I), After:=.Cells(.Cells.Count), _
LookIn:=xlFormula, LookAt:=xlWhole, _
SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Interior.ColorIndex = myColor(I)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <>
FirstAddress
End If
Next I
End With
End If
End Sub

Thanks in advance for your help

Eli

 

[Simon Lloyd]

You can change this line
Code:
--------------------
Rng.Interior.ColorIndex = myColor(I)
--------------------
for this one

Code:
--------------------
Range("A" & Rng.Row & ":T" & Rng.Row).Interior.ColorIndex = myColor(I)
--------------------

Hi,

I am using the following code in order to color a cell in one worksheet
if
the same value was typed in another worksheet.
it works fine when I am entering one value at the time and I am
wondering if
it is possible to do the same if i will copy and paste several values
at the
time. also it will be grate if i will be able to color not only the
matching
cell but the range between A:T in the relevant row.

Thanks to Ron De Bruin for the code: Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Worksheets("Pouch log")
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As Variant
Dim lnLastRow As Long
Dim Rng As Range
Dim I As Long
lnLastRow = Cells(Rows.Count, "B").End(xlUp).Row
If Target.Address = Range("B" & lnLastRow).Address Then
MySearch = Array(Range("B" & lnLastRow))
myColor = Array("3")
lnLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
With ws.Range("A1:Z" & lnLastRow)
For I = LBound(MySearch) To UBound(MySearch)
Set Rng = .Find(What:=MySearch(I), After:=.Cells(.Cells.Count), _
LookIn:=xlFormula, LookAt:=xlWhole, _
SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Interior.ColorIndex = myColor(I)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <>
FirstAddress
End If
Next I
End With
End If
End Sub
--------------------
Thanks in advance for your help

Eli

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)