i need macro help

[project manager]

I am trying to make a macro which will conditionally format a cell in range
c5:c27 in worksheet alpha, when the text entered does not match a text
entered in a much longer list (A1:A947) in a different worksheet (delta). I
dont think i can use a data validation because i need the users to be able to
enter a text/value but it just flag up to me that its wrong. the format when
its wrong/ different from the list is text colour black, bold italic, cell
colour red.

any help would be awesome.

 

[Joel]

I would recommend using a worksheet change function. You ae right that data
validation won't acknowledge the wrong value entered. Conditional formating
is difficult to return the results. You can't look at the color index of the
cell because condition formating while it looks like the color index was
changed, it is a differnt property that needs to be check to determine if the
condition was met.

 

[Tim Williams]

Name your range of allowed values on the "delta" sheet (eg "LIST")

Used conditional formatting on the data entry range
Formula: =AND(A1<>"",ISERROR(MATCH(A1,LIST,0)))
Set the "error" format however you want.

Tim