Data Validation- Case Sensitivity

[gvoisard]

I understand that you cannot check for case sensitivity when you are referencing a source for your data validation. However, I cannot type in a list because I will use more than 255 characters.

Is there a way that I can use conditional formatting to turn the cell red if it does not exactly match a cell that is in the list that I am referencing for my data validation?

 

[dreid1011]

How about this, if your validation list is housed on a sheet somehere.

Book1
	A	B	C
1	Test		Colors
2	green		Red
3	Blue		Green
4	Black		Blue
5	White		Yellow
6	Orange		Orange
7	Green		Purple
8	Teal		Violet
9	Brugundy		Teal
10	Red		Burgundy
11	Lavender		Lavender
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=IFERROR(EXACT($A2,INDEX($C$2:$C$11,MATCH($A2,$C$2:$C$11,0))),FALSE)=FALSE	text	NO

 

[gvoisard]

How about this, if your validation list is housed on a sheet somehere.

Book1
	A	B	C
1	Test		Colors
2	green		Red
3	Blue		Green
4	Black		Blue
5	White		Yellow
6	Orange		Orange
7	Green		Purple
8	Teal		Violet
9	Brugundy		Teal
10	Red		Burgundy
11	Lavender		Lavender
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=IFERROR(EXACT($A2,INDEX($C$2:$C$11,MATCH($A2,$C$2:$C$11,0))),FALSE)=FALSE	text	NO

Sorry I was not very clear. All of the cells that I am referencing (Column C in this case) are capitalized. If there is any cells that have lowercase letters in column A, I want the cell to highlight red.

 

[dreid1011]

Sorry I was not very clear. All of the cells that I am referencing (Column C in this case) are capitalized. If there is any cells that have lowercase letters in column A, I want the cell to highlight red.

Does this not do what you want? Look at the first red cell, "green" vs "Green".

 

[gvoisard]

Does this not do what you want? Look at the first red cell, "green" vs "Green".

It is not. Do you see anything wrong of the top of your head?

 

[dreid1011]

View attachment 98572

It is not. Do you see anything wrong of the top of your head?

What is that between the = and ISERROR? Looks like quotes or an asterisk? There shouldn't be any quotes around the formula. Also, I used IFERROR, not ISERROR.

 

[dreid1011]

What is that between the = and ISERROR? Looks like quotes or an asterisk? There shouldn't be any quotes around the formula. Also, I used IFERROR, not ISERROR.

Copy this formula exactly:

=IFERROR(EXACT($K2,INDEX('Formula Lists'!$K$2:$K$134,MATCH($K2,'Formula Lists'!$K$2:$K$134,0))),FALSE)=FALSE

 

[Peter_SSs]

I understand that you cannot check for case sensitivity when you are referencing a source for your data validation.

Why not? Isn't this the sort of thing you are trying to achieve?
My DV list is in B2:B4
DV as shown in A2:A5
I have typed "THREE" into cell A2 and that has been allowed.

23 09 12.xlsm
	A	B
1
2	THREE	ONE
3		TWO
4		THREE
5
DV

Cells with Data Validation
Cell	Allow	Criteria
A2:A5	Custom	=EXACT(A2,FILTER($B$2:$B$4,$B$2:$B$4=A2,""))

If I try to enter Three into A3 this is what I get

 

[dreid1011]

Is there a way that I can use conditional formatting to turn the cell red if it does not exactly match a cell that is in the list that I am referencing for my data validation?

Did the formula I suggested work for the highlighting or was that not what you were really after?

 

[gvoisard]

Did the formula I suggested work for the highlighting or was that not what you were really after?

It worked thanks