Conditional Format - check text is in list 1

[osx99]

Hello,

In excel i want to turn a cell red when text in a cell doesn't match text in a list.

I've looked at array formulas and the help files says to enter the following as an ARRAY FORMULA by pressing CTRL+SHIFT+ENTER

{=OR(EXACT(TestValue, CompareRange))}

I have tried {=OR(EXACT(A47, B4:B34))}

but I don't seem to be able to get this formula to work? CTRL+SHIFT+ENTER has no effect??

Can anyone advise

Thanks,
Osx

 

[SkipVought]

Hi,

Why not use Data/Validation - List.

This will PREVENT a value from being entered that is NOT on your list.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[osx99]

SkipVought - Thanks good Idea

However, I'm still baffled about what to put in the data/validation formula to get it to check against a list?

Osx

 

[SkipVought]

Lets say that you list is in AA1:AA3 AND your list has a Range Name, MyList

Then in the Data/Validation - Allow List, SOURCE...

Either
[red]=$AA$1:$AA$3[/red]

or
[red]=MyList[/red]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[osx99]

SkipVought - thanks problem solved

However, I now have another q.

The Data/Validation - Allow List, SOURCE methos works a treat and if I try to enter text it gives an error message. However, if I copy and paste as value into the cell it will accept the data even though the validation is not correct.

Is there a way around this as I envisage a lot of cutting and pasting of data which I still need to be validated?

MAny thanks,
Osx

 

[SkipVought]

If you noticed, the copy/paste "destroyed" the validation because that operation COPIES and PASTES the FORMAT as well as the VALUE and part of the FORMAT is Data Validation.

You will have a problem with ANY spreadsheet feature approch if you copy/paste, because whatever formatting is used as a criteria is DESTROYED by the copy/paste operation.

THe only OTHER approch is a probramatic VBA code option. Do you want to try this?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[osx99]

SkipVought

The criterea is still there since I'm only pasting as value and if i then go back to the cell and type it then produces the error. I agree if you paste all it will destroy the criteria.

It looks like VBA code is the only way in this case so any help would be much appreciated. I assume it would be in some kind of Macro that runs after an update? I've more experience using VBA in Access so need help on the Excel side

Thanks,
Osx

 

[SkipVought]

It will be triggered by the Worksheet_Change event. Try some code like this...

Private Sub Worksheet_Change(ByVal Target As Range)
  ptr = application.match(Target.Value, Range("LookupList"),0)
  if iserror(ptr) then
    Target.Interior.Color = vbRed
  end if
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[osx99]

SkipVought - Wonderful Thanks for your help

 

[osx99]

Ok slight problem - my code works fine and turns a cell red if text from the list is not entered into the cells. However, copying and pasting still bypasses it and doesn't get picked up by the Worksheet_Change event?

Any ideas?

Thanks,
Osx

 

[SkipVought]

Ahh, you may be copy/pasting MORE THAN ONE CELL MAYBE?

Private Sub Worksheet_Change(ByVal Target As Range)
for each t in target
  ptr = application.match(t.Value, Range("LookupList"),0)
  if iserror(ptr) then
    t.Interior.Color = vbRed
  end if
next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884