Look up match and highlight

A

almayzan

Joined
Apr 26, 2011
Messages
4
Reaction score
0
I need to create macro on excel that will compare two worksheets .
The macro should start with the first worksheet, using the first entry in (Column K) and look for this number in the second worksheet.
If found, the macro should then verify the number from column matches between both worksheets.
If the macro finds the match between the two worksheets, it should highlight or gray out the rows on each spreadsheet.
If a match is not found, the macro should increment down to the next row number in the first worksheet without highlighting or graying out anything
 
i have this code written but am still struggling with these points
1. need to fix the filter function by keeping only the number i search for
2.i need to confirm that these number are matching up
3. need to highlight the active cell row and the code is highting the first row
here is the code

Sub Find_Data()
' Written by almayzan
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter UF ")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
On Error Resume Next
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$CZ$268").AutoFilter Field:=datatoFind, Criteria1:="datatoFind"

currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the Breaker Number ")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
Rows("ActiveCell.Value").Select
Range("ActiveCell.Value").Activate
ActiveWindow.SmallScroll Down:=9
Selection.Find(What:="* ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Sheets("PLANNING").Select
Range("C7:H7").Select
Cells.FindNext(After:=ActiveCell).Activate
Range("C7:H7").Select
Sheets("CASCADE").Select

Rows("ActiveCell.Value").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End Sub
 
Back
Top