I I need to highlight a range of cells each containting a letter if the word they create is a real english word

[extrahotfudge]

Okay, so I have this Caesar Cipher set up with a spinner (not shown) and I want to make it so that when H1&I1&J1&K1&L1&M1 is a real word. I have a seperate sheet with an imported .txt file of english words, and I used VLOOKUP to determine when it was a real word. I was wondering if there is a way I could highlight H1:M1 when it is a word. I was thinging this could be done with conditional formatting but have been having trouble. When H1&I1&J1&K1&L1&M1 does not match something on the sheet of words, then G7 returns an error.

Any help is appreciated thank you!

lab7.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Z	O	Y	Z	V	O		P	E	O	P	L	E
2
3
4
5	SHIFT VALUE:	16
6							PEOPLE
7							people
8
9
10
11
12
13
14
15
16
17
18
19
20
Caesar

Cell Formulas
Range	Formula
H1:M1	H1	=CHAR(MOD(CODE(A1)-65+$B$5,26)+65)
G6	G6	=H1&I1&J1&K1&L1&M17&M1
G7	G7	=VLOOKUP(H1&I1&J1&K1&L1&M17&M1,'English Words'!A:A,1,FALSE)

Named Ranges
Name	Refers To	Cells
'English Words'!american_english	='English Words'!$A$1:$A$98568	G7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:M1	Expression	=EXACT("H1&I1&J1&K1&L1&M1","G7")	text	NO
G7	Cell Value	duplicates	text	NO

 

[etaf]

have you tried a countif('English Words'!A:A, H1&I1&J1&K1&L1&M1 ) > 0
then if it does exist as a word - then count should be 1

And use that as the conditional formatting
No need for a vlookup()
That will return an ERROR if not found or the word
the
countif('English Words'!A:A, H1&I1&J1&K1&L1&M1 ) > 0
will return TRUE or FALSE

you could modify vlookup to use ISERROR()

Book10
	A	B	C	D	E	F
1						aaa
2						abd
3	TRUE	a	b	d		ace
4	TRUE	a	c	e		abc
5	FALSE	m	n	p		yxz
6	FALSE					bbb
7	TRUE	c	c	c		ccc
8						dd
Sheet1

Cell Formulas
Range	Formula
A3:A7	A3	=COUNTIF($F$1:$F$8,B3&C3&D3)>0

 

[extrahotfudge]

This worked, but only highlighted the first letter... How could I make it highlight all 6? This is what I have as the rule:

 

[etaf]

countif('English Words'!$A:$A, $H1&$I1&$J1&$K1&$L1&$M1 ) > 0

Try selecting the rang H1:M1000
or however many rows
and the $ will fix and highlight them all

Book10
	A	B	C	D	E	F
1						aaa
2						abd
3	TRUE	a	b	d		ace
4	TRUE	a	c	e		abc
5	FALSE	m	n	p		yxz
6	FALSE					bbb
7	TRUE	c	c	c		ccc
8						dd
Sheet1

Cell Formulas
Range	Formula
A3:A7	A3	=COUNTIF($F$1:$F$8,B3&C3&D3)>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:D8	Expression	=COUNTIF($F$1:$F$8,$B3&$C3&$D3)>0	text	NO

 

[etaf]

you may need to include a AND() to stop Blanks matching a blank cell in words
=AND(COUNTA($H1:$M1)>0,COUNTIF('English Words'!$A:$A, $H1&$I1&$J1&$K1&$L1&$M1 )>0)

I have added the sheet - English names

Book10
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Z	O	Y	Z	V	O		P	E	O	P	L	E
2
3
4
5	SHIFT VALUE:	16
6							PEOPLE
7							#N/A
Sheet2

Cell Formulas
Range	Formula
H1:M1	H1	=CHAR(MOD(CODE(A1)-65+$B$5,26)+65)
G6	G6	=H1&I1&J1&K1&L1&M17&M1
G7	G7	=VLOOKUP(H1&I1&J1&K1&L1&M17&M1,'English Words'!A:A,1,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H1:M3,O1:O5	Expression	=AND(COUNTA($H1:$M1)>0,COUNTIF('English Words'!$A:$A, $H1&$I1&$J1&$K1&$L1&$M1 )>0)	text	NO

 

[extrahotfudge]

countif('English Words'!$A:$A, $H1&$I1&$J1&$K1&$L1&$M1 ) > 0

Try selecting the rang H1:M1000
or however many rows
and the $ will fix and highlight them all

Book10
	A	B	C	D	E	F
1						aaa
2						abd
3	TRUE	a	b	d		ace
4	TRUE	a	c	e		abc
5	FALSE	m	n	p		yxz
6	FALSE					bbb
7	TRUE	c	c	c		ccc
8						dd
Sheet1

Cell Formulas
Range	Formula
A3:A7	A3	=COUNTIF($F$1:$F$8,B3&C3&D3)>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:D8	Expression	=COUNTIF($F$1:$F$8,$B3&$C3&$D3)>0	text	NO

Worked! Thank you!

 

[etaf]

you are welcome