Please Help

K

K

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("Sheet2").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..........
 
R

Ron de Bruin

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("Sheet2").Columns("A:C"), Target.Value) = 0 Then
MsgBox "not exist in Sheet2"
End If
End If
End Sub
 
J

JLGWhiz

Can you please help me with second macro
which i stated above
Click to expand...

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("Sheet2") _
.Range("A2:C" & lr), Target.Value) = 0 Then _
MsgBox "not exist in Sheet2"
Else
x = Application.WorksheetFunction.CountIf(Sheets("Sheet2") _
.Range("A2:C" & lr), Target.Value)
MsgBox x
End If
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copy rows based on cell colour by conditional formating 8
Excel 2013 Merge Data 2
Conflict 8
VBA set thick borders around non empty cells in range 0
copy rows to next sheet 3
NEED MACRO FOR COLOUR BY CONDITIONAL FORMATTING 9
NEED CORRECTION IN MACRO 2
Macro help needed 4

Top