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

extrahotfudge

New Member
Joined
Apr 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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
ABCDEFGHIJKLM
1ZOYZVOPEOPLE
2
3
4
5SHIFT VALUE:16
6PEOPLE
7people
8
9
10
11
12
13
14
15
16
17
18
19
20
Caesar
Cell Formulas
RangeFormula
H1:M1H1=CHAR(MOD(CODE(A1)-65+$B$5,26)+65)
G6G6=H1&I1&J1&K1&L1&M17&M1
G7G7=VLOOKUP(H1&I1&J1&K1&L1&M17&M1,'English Words'!A:A,1,FALSE)
Named Ranges
NameRefers ToCells
'English Words'!american_english='English Words'!$A$1:$A$98568G7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1:M1Expression=EXACT("H1&I1&J1&K1&L1&M1","G7")textNO
G7Cell ValueduplicatestextNO
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,683
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEF
1aaa
2abd
3TRUEabdace
4TRUEaceabc
5FALSEmnpyxz
6FALSEbbb
7TRUEcccccc
8dd
Sheet1
Cell Formulas
RangeFormula
A3:A7A3=COUNTIF($F$1:$F$8,B3&C3&D3)>0
 

extrahotfudge

New Member
Joined
Apr 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
This worked, but only highlighted the first letter... How could I make it highlight all 6? This is what I have as the rule:
 

Attachments

  • Screen Shot 2021-04-09 at 3.42.10 PM.png
    Screen Shot 2021-04-09 at 3.42.10 PM.png
    75.2 KB · Views: 1

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,683
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEF
1aaa
2abd
3TRUEabdace
4TRUEaceabc
5FALSEmnpyxz
6FALSEbbb
7TRUEcccccc
8dd
Sheet1
Cell Formulas
RangeFormula
A3:A7A3=COUNTIF($F$1:$F$8,B3&C3&D3)>0
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:D8Expression=COUNTIF($F$1:$F$8,$B3&$C3&$D3)>0textNO
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,683
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
ABCDEFGHIJKLM
1ZOYZVOPEOPLE
2
3
4
5SHIFT VALUE:16
6PEOPLE
7#N/A
Sheet2
Cell Formulas
RangeFormula
H1:M1H1=CHAR(MOD(CODE(A1)-65+$B$5,26)+65)
G6G6=H1&I1&J1&K1&L1&M17&M1
G7G7=VLOOKUP(H1&I1&J1&K1&L1&M17&M1,'English Words'!A:A,1,FALSE)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1:M3,O1:O5Expression=AND(COUNTA($H1:$M1)>0,COUNTIF('English Words'!$A:$A, $H1&$I1&$J1&$K1&$L1&$M1 )>0)textNO
 

extrahotfudge

New Member
Joined
Apr 9, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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
ABCDEF
1aaa
2abd
3TRUEabdace
4TRUEaceabc
5FALSEmnpyxz
6FALSEbbb
7TRUEcccccc
8dd
Sheet1
Cell Formulas
RangeFormula
A3:A7A3=COUNTIF($F$1:$F$8,B3&C3&D3)>0
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:D8Expression=COUNTIF($F$1:$F$8,$B3&$C3&$D3)>0textNO
Worked! Thank you!
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,683
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,130,214
Messages
5,640,910
Members
417,178
Latest member
HelpMeExcelExperts

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top