Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........



Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.


Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........

Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........



Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.


Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........

thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........



Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.


Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........

Read
http://www.cpearson.com/excel/CFColors.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........



Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.


Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........

Can you please help me with second macro
which i stated above

The second macro was fixed in your other thread.

Here is a modified version of the code for the first macro. It will tell
you how many of the Target values are found in Sheet 2 if any are found, as
well as telling you if they are not found at all. It does not tell you what
column the value is found in in sheet 2. Since you provide very little
information about your project, I assume that detail does not matter.

Private Sub Worksheet_Change(ByVal Target As Range)
lr = Worksheets(2).Cells.Find(What:="*", After:=Range("A1"), _
LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False).Row
If Target.Column < 4 And Target.Value 0 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2") _
.Range("A2:C" & lr), Target.Value) = 0 Then _
MsgBox "not exist in Sheet2"
Else
x = Application.WorksheetFunction.CountIf(Sheets("Shee t2") _
.Range("A2:C" & lr), Target.Value)
MsgBox x
End If
End If
End Sub


"K" wrote:

thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........



Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.


Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........

On 15 Dec, 18:07, "Ron de Bruin" wrote:
Readhttp://www.cpearson.com/excel/CFColors.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"K" wrote in ...
thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........

Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........- Hide quoted text -

- Show quoted text -

Thanks Ron this site got quite useful information but i tried many
ways but when i run macro the error message comes "out of range" and
in macro window "If c.FormatConditions(1).Interior.ColorIndex = 3 Then
" this line get yellow colour. Please state how can i amend my macro

Work with JLGWhiz

He already posted code for you that you can try

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
On 15 Dec, 18:07, "Ron de Bruin" wrote:
Readhttp://www.cpearson.com/excel/CFColors.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"K" wrote in ...
thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........

Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........- Hide quoted text -

- Show quoted text -

Thanks Ron this site got quite useful information but i tried many
ways but when i run macro the error message comes "out of range" and
in macro window "If c.FormatConditions(1).Interior.ColorIndex = 3 Then
" this line get yellow colour. Please state how can i amend my macro

On 15 Dec, 18:29, "Ron de Bruin" wrote:
Work with JLGWhiz

He already posted code for you that you can try

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"K" wrote in ...
On 15 Dec, 18:07, "Ron de Bruin" wrote:
Readhttp://www.cpearson.com/excel/CFColors.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"K" wrote in ...
thanks for replying Ron. Can you please help me with second macro
which i stated above

Ron de Bruin wrote:
Please stay in the original thread

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"K" wrote in ...
Hi mate, Please i need macro for which i'll be very thank ful to
please do reply

i want the macro that if i put any value in any cell of coloumns
A , B and C in sheet 1 then it lookup that value in coloumns A , B
and
C of sheet 2 and if value is different then message should come up
that value not found. is there any way to make this kind of macro. i
received the macro but this only work for one coloumn I want to get
macro to lookup in three coloums. (Please see you macro below)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If
Application.WorksheetFunction.CountIf(Sheets("Shee t2").Columns("B"),
Target.Value) =0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub

Please help. Thanks..........

Hi mate, Please i need macro for which i'll be very thank ful to
please do reply
I get "Interior.Colorindex = 3" or Red colour in coloumn B cells
by conditional formatting. I have put formula in conditional
formatting that when if value of cell in coloumn B is True by formula
then cell get Red colour. One of my online friend send me the macro
(please see below) which work fine but little problem that instead of
coping only Red coloured cells by conditional formatting it copies
all
sheet1 data to sheet2. I want macro to copy only those cells rows
which got Red colour by conditional formatting. and i dont want
entire row just from cell A to cell F.

Sub cpyColr()
Dim c As Range
lastRw = Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Worksheets(1).Range("B2:B" & lastRw)
If c.FormatConditions(1).Interior.ColorIndex = 3 Then
lstRw2 = Worksheets(2).Range("A65536").End(xlUp).Row
cRng = c.Address
Worksheets(1).Range("A" & Range(cRng).Row & ":F" &
Range(cRng).Row).Copy _
Worksheets(2).Range("A" & lstRw2 + 1)
End If
Next
End Sub

Please help. Thanks..........- Hide quoted text -

- Show quoted text -

Thanks Ron this site got quite useful information but i tried many
ways but when i run macro the error message comes "out of range" and
in macro window "If c.FormatConditions(1).Interior.ColorIndex = 3 Then
" this line get yellow colour. Please state how can i amend my macro- Hide quoted text -

- Show quoted text -

thanks Ron