Problem with use of FIND

[stoggers]

Hi,

How do I use FIND in VBA?

What I have is two columns, say A & B, and I would like to highlight (ie change the color of) cells in A if they exist in B.

Would FIND be an appropriate way of doing this?

Does anyone have any code-snippets that could do this?

Any suggestions appreciated.

Regards,

Mike.

 

[bryanbayfield]

Sounds like you could do this with conditional formatting rather than VBA.

Try looking up help in Excel.

 

[stoggers]

Hi,

I was creating & populating the spreadsheet using VBA therefore I would like to do the formatting in VBA.

Mike

 

[bryanbayfield]

OK, would like to clarify what you mean by changing the colour of cells in column B if they exist in A?

You want to go through all the cells in column B and if the value of that cells matches a value to be found in column A then change the colour of the cell in column B?

 

[stoggers]

Hi,

Yes...

Mike.

 

[xlbo]

Stoggers - actually that's the opposite way round to your original request ;-). I'll leave Bryan to give you the code for this tho

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[bryanbayfield]

Private Sub CommandButton1_Click()
Dim look_inS As String, a1Data() As String, a2Raw() As Variant, i As Integer, c As Range
ActiveSheet.UsedRange
a2Raw = ActiveSheet.UsedRange.Columns(1).Value
ReDim a1Data(UBound(a2Raw, 1))
For i = 1 To UBound(a2Raw)
    a1Data(i) = a2Raw(i, 1)
    Next
look_inS = Trim(Join(a1Data))
For Each c In ActiveSheet.UsedRange.Columns(2).Cells
    If InStr(look_inS, c) > 0 Then c.Interior.ColorIndex = 3
    Next
End Sub