SOLVED

Conditional Formatting Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2845515%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2845515%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20want%20to%20fill%20a%20cell%20green%20if%20the%20word%20in%20the%20cell%20in%20found%20in%20a%20column.%3C%2FP%3E%3CP%3EFor%20example%3A%20the%20word%20is%20in%20cell%20L5%20and%20I%20want%20to%20search%20the%20cells%20C5%20-%20C80.%20If%20the%20word%20in%20L5%20appears%20in%20C5-C80%2C%20I%20want%20L5%20to%20turn%20green.%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20can%20it%20then%20be%20filled%20down%20so%20L6%20can%20then%20be%20searched%20in%20cells%26nbsp%3BC5%20-%20C80%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2845515%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2845615%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2845615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1185372%22%20target%3D%22_blank%22%3E%40breely2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20all%20the%20cells%20that%20you%20want%20to%20format%2C%20for%20example%20L5%3AL20%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DISNUMBER(MATCH(L5%2C%24C%245%3A%24C%2480%2C0))%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20green.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 

I am want to fill a cell green if the word in the cell in found in a column.

For example: the word is in cell L5 and I want to search the cells C5 - C80. If the word in L5 appears in C5-C80, I want L5 to turn green.

Is this possible?

 

If so, can it then be filled down so L6 can then be searched in cells C5 - C80?

 

Thanks in advance for your help.

2 Replies
best response confirmed by breely2021 (New Contributor)
Solution

@breely2021 

Select all the cells that you want to format, for example L5:L20

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=ISNUMBER(MATCH(L5,$C$5:$C$80,0))

Click Format...

Activate the Fill tab.

Select green.

Click OK, then click OK again.

@Hans Vogelaar Thank you so much. This is exactly what I am after.