Macro to check if any names put in Column H are also in the text of Column F

[tonywatsonhelp]

Hi Everyone,
I'd like a macro to run a check and bring up a warning if the data in any of the cell in Range H11:H223 appears in the text of the cell befor it in Range F11:F223 but only the cell on the same row for each word

Ok so I have a document sheet called "Report Data"

Column F is called "Details" and Column H is Called "Department" (there is a lot more to this document but basically that all the data needed to do what I want)

So Column H has private Information in it that must not appear on so reports we send, the problem is sometime the data in Column H is being refered to it column F

So For example:

H might say "Mr Randhall",

now in F is a description that should not include that word, however sometime it does, so it might read

" I spoke to Mr Randhall and we are meeting next Tuesday"
it should read

" I spoke to Client and we are meeting next Tuesday"

so what I need is a macro to check if the private data in each row of H is not in the public comments in F

I realise there are lots of complication to this but I just want it simply to check the word and see if it a match to any of the words in the sentence.

if it does A message box should pop up say "Private Data in Details, please remove"

any ideas how I can do this???
tony

 

[seguin85]

I would recommend doing something to the cell itself (colour red, bold, etc). This way your code will not stop each time there is confidential data and you have to change it. It will loop thorough the whole list and colour all of those incidents. Anyways, this code should do the trick:

Sub Macro1()
Dim i As Integer
Dim lookfor As String


i = 2


Do Until IsEmpty(Cells(i, 8))
    lookfor = Cells(i, 8)
    If Cells(i, 6) Like "*" & lookfor & "*" Then
        MsgBox "Private data in " & Cells(i, 6).Address & " Please remove"
        Cells(i, 6).Interior.ColorIndex = 3
    End If
        
    i = i + 1
Loop


End Sub

 

[tonywatsonhelp]

Thank you very much Seguin85 this is excellent,

having the two cell turn red if this happened would be a great idea, any idea how I would do that

 

[seguin85]

Thank you very much Seguin85 this is excellent,

having the two cell turn red if this happened would be a great idea, any idea how I would do that

Sub Macro1()

Dim i As Integer
Dim lookfor As String

i = 2

Do Until IsEmpty(Cells(i, 8))

lookfor = Cells(i, 8)
If Cells(i, 6) Like "*" & lookfor & "*" Then
Cells(i, 6).Interior.ColorIndex = 3
Cells(i, 8).Interior.ColorIndex = 3
End If

i = i + 1
Loop


End Sub