VBA check if string contains character from list

[Kra]

Hi all,
I am trying to set up macro which will detect character from list supplied by user in a cell value.

I have 2 sheets: "Data" and "Characters".

In "Characters" user will in write characters which they want to be detected (all in column A, no header). So lets say they want to detect capital O, dash and number 9.
A1 O
A2 -
A3 9

They will paste some report/data dump in sheet called "Data" and then macro will check each cell and highlight it on red if cell value contains any of characters given by user.

 

[Peter_SSs]

If I have understood correctly, then without looping through each character, each column and each row I think this should do it all at once.
I have assumed that the character list in column A of the 'Characters' sheet does not contain any blank cells.

Sub CheckCharacters()
  With Sheets("Data").UsedRange
    .FormatConditions.Add Type:=xlExpression, Formula1:=Replace(Replace("=COUNT(FIND(Characters!$A$1:$A$#,^))", "#", Sheets("Characters").Cells(Rows.Count, 1).End(xlUp).Row), "^", .Cells(1, 1).Address(0, 0))
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub