On Apr 7, 10:22*am, Office_Novice
<OfficeNov...@discussions.microsoft.com> wrote:
> you're earlier post only returnedthe Cell adress in the msgbox. *I needed
> somthing more like this
>
> Sub compareVals()
>
> * Dim Cell As Range
> * With Worksheets(1).Range("C2:C60000")
>
> * Do
> * On Error Resume Next
> * Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues)
>
> * * If Not Cell Is Nothing Then
> * * * * Cell.Interior.ColorIndex = 6
> * *ElseIf Cell Is Nothing Then
> * * * ActiveCell.Interior.ColorIndex = 3
> * * End If
> * * *ActiveCell.Offset(1, 0).Select
> * * Loop Until ActiveCell = ""
> * End With
> End Sub
>
>
>
> "Ivyleaf" wrote:
> > On Apr 7, 8:25 am, Office_Novice
> > <OfficeNov...@discussions.microsoft.com> wrote:
> > > That didnt work at all. Thanks for trying though.
>
> > > I have written this & it does what i want but only if i manualy changethe
> > > active cell
> > > stop and restart the macro Could use some help modifying the code
>
> > > Option Explicit
>
> > > Sub compareRng()
>
> > > * Dim Cell As Range
> > > * With Worksheets(1).Range("C2:C6000")
> > > * Set Cell = .Find(What:=ActiveCell.Value, LookIn:=xlValues)
>
> > > * Do
> > > * * If Not Cell Is Nothing Then
> > > * * * * Cell.Interior.ColorIndex = 6
> > > * * ElseIf Cell Is Nothing Then
> > > * * * * ActiveCell.Interior.ColorIndex = 3
> > > * * End If
> > > * * *ActiveCell.Offset(1, 0).Select
> > > * *Loop
> > > * End With
> > > End Sub
>
> > > "Ivyleaf" wrote:
> > > > On Apr 6, 7:41 am, Office_Novice
> > > > <OfficeNov...@discussions.microsoft.com> wrote:
> > > > > greetings
>
> > > > > i am stuck, i have two lists, both with the same information, but in
> > > > > differnt places what i need is *this
>
> > > > > If Cell A1. value = "Anything on sheet (2) Column A" then do nothing
>
> > > > > else if Cell A1. Value <> *"Anything on sheet (2) Column A" then
>
> > > > > highlight *A1, I know not too tough, but here is where i get stuck..
>
> > > > > After searching for Cell A1.value i need to search the rest of column A's
> > > > > cells against Sheet (2) Column A
>
> > > > > any help would be great.
>
> > > > Hi,
>
> > > > Sub compareVals()
> > > > * Dim SrcList As Range, ChkList As Range
> > > > * Dim cell As Range, FoundRng As Range
>
> > > > * Set SrcList = Range("C1:C19")
> > > > * Set ChkList = Range("A1:A19")
>
> > > > * On Error Resume Next
> > > > * For Each cell In SrcList
> > > > * * Set FoundRng = ChkList.Find(cell)
> > > > * * If FoundRng Is Nothing Then
> > > > * * * MsgBox "No match for " & cell & " in " & ChkList.Address
> > > > * * * Else
> > > > * * * MsgBox "Found " & cell & " in " & ChkList.Address
> > > > * * End If
> > > > * *Next
> > > > End Sub
>
> > > > This will loop through one range and let you know if it finds the cell
> > > > from range 1 in range 2 or not. Just change the definition of the
> > > > ranges to match you needs.
>
> > > > Cheers,
> > > > Ivan.- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi,
>
> > I'm puzzled that the code I posted didn't work for you. When you say
> > it didn't work, do you mean you were getting an error, or that it
> > wasn't finding anything or something else?
>
> > I would say if it didn't find what you expected, that you haven't
> > redefined the ranges quickly.
>
> > Using my original code, you would have to change the following lines:
>
> > * Set SrcList = Range("C1:C19")
> > * Set ChkList = Range("A1:A19")
>
> > to:
>
> > * Set SrcList = Sheets(???).Range("???:???")
> > * Set ChkList = Sheets(1).Range("C2:C6000")
>
> > Just replace the question marks with the correct range... you haven't
> > said where the list of values you want to check for is located.
>
> > Cheers,
> > Ivan.- Hide quoted text -
>
> - Show quoted text -
Hi,
Sorry, I obviously didn't clarify my initial code properly. It was
only designed to be a proof of concept as I thought from the sound of
your post the main trouble you were having was with looping through
the list and finding the values. As such, I thought you could just
replace the appropriate msgbox line with whatever you wanted to happen
- i.e. change colour of the cell.
The following code should (maybe) be more suitable:
Sub compareVals()
Dim SrcList As Range, ChkList As Range
Dim cell As Range, FoundRng As Range
Set SrcList = Intersect(Sheets(2).Columns(1),Sheets(2).UsedRange)
Set ChkList = Intersect(Sheets(1).Columns(3),Sheets(1).UsedRange)
On Error Resume Next
For Each cell In SrcList
Set FoundRng = ChkList.Find(cell, LookAt:=xlWhole)
If FoundRng Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next
For Each cell In ChkList
Set FoundRng = SrcList.Find(cell, LookAt:=xlWhole)
If FoundRng Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next
End Sub
|
Similar Threads
