# Format cell based on list



## John_Whin (Dec 29, 2022)

Creating conditional formatting based on letter in a list. This is for Wordle and I'm trying to highlight with cell color letters that are wrong. So see in image my first two starting words, for this example I've entered CONES and TRAIL as my first two words. So for this example the only correct letters are "O" "A" and "I". So I've entered the wrong letters in column F and will continue to add to column F more wrong letters with additional words.

Anytime I try to use a wrong letter that I put in the list in column F in any cell from A2 -> E26 I want to color the cell Blue. So it would look like this example image. When I enter a new word like CAIRN I want it to automatically format the wrong letters that I will continue to add to column F. I want it to do the same thing with my alphabet from H1 -> AG1

Been a while since I ask anything here and the Moderator Fluff has helped me many times before.

Thanks! I'll check back in a couple hours.


----------



## Joe4 (Dec 30, 2022)

Assuming that you only get 6 guesses, like you do in standard Wordle games, select the range A2:E7, go to Conditional Formatting, select the Formula option (last one), and enter this formula:

```
=AND(A2<>"",COUNTIF($F$2:SF$27,A2)>0)
```
and choose your desired color fill option.

This should do exactly what you want.


----------



## jdellasala (Dec 30, 2022)

Mr. Excel has no less than THREE videos on Wordle:
*WORDLE Helper In Excel - Solve Wordle Faster! - Episode 2462*
*Answering Wyn Hopkins Wordle Challenge*
*Excel Doing Billions Of Calculations To Study Wordle*


----------



## John_Whin (Dec 30, 2022)

jdellasala said:


> Mr. Excel has no less than THREE videos on Wordle:
> *WORDLE Helper In Excel - Solve Wordle Faster! - Episode 2462*
> *Answering Wyn Hopkins Wordle Challenge*
> *Excel Doing Billions Of Calculations To Study Wordle*


The gal who introduced me to Wordle a couple weeks ago and I agreed not to use any online helper other than a dictionary. I don't use the 2 words I have in this example since they came from a suggested starting pair online. She has an excel spreadsheet of her own that she used for months and I'm trying to create one for myself. That being said I will look at what you suggested out of curiosity but not use them since for us anyway that would take some of the fun out of it. Thanks though.


----------



## John_Whin (Dec 30, 2022)

Joe4 said:


> Assuming that you only get 6 guesses, like you do in standard Wordle games, select the range A2:E7, go to Conditional Formatting, select the Formula option (last one), and enter this formula:
> 
> ```
> =AND(A2<>"",COUNTIF($F$2:SF$27,A2)>0)
> ...


----------



## John_Whin (Dec 30, 2022)

Joe4 said:


> Assuming that you only get 6 guesses, like you do in standard Wordle games, select the range A2:E7, go to Conditional Formatting, select the Formula option (last one), and enter this formula:
> 
> ```
> =AND(A2<>"",COUNTIF($F$2:SF$27,A2)>0)
> ...


Joe4, This is exactly what I needed for the first part! How can I adapt it to do the same thing across H1 -> AG1? Thanks very much, John


----------



## Joe4 (Dec 30, 2022)

John_Whin said:


> Joe4, This is exactly what I needed for the first part! How can I adapt it to do the same thing across H1 -> AG1? Thanks very much, John


Select cells H1:AG1 and use this Conditional Formatting formula:

```
=COUNTIF($F$2:SF$27,H1)>0
```


----------



## John_Whin (Dec 30, 2022)

Joe4 said:


> Select cells H1:AG1 and use this Conditional Formatting formula:
> 
> ```
> =COUNTIF($F$2:SF$27,H1)>0
> ```


Perfect, Thank you again! John


----------



## Joe4 (Dec 31, 2022)

You are welcome.
Glad I was able to help!


----------



## John_Whin (Dec 31, 2022)

Joe4 said:


> You are welcome.
> Glad I was able to help!


I may want to try one more thing if I can describe it better in a few days, I'll reply to this again so you'll see it if you are willing to help more. Thanks for your patience.


----------

