Conditional formating to highlight cells which contain text from other cells

Conditional formating to highlight cells which contain text from other cells

Effective I need a player search tool.

To explain, I have a list of football starting line ups for 17 games. When we play the next matches I want to be able to put the 11 names from the days match into the Matchday list which i have created to quickly see when they have played in the previous matches.


so in the cell range A2 - Q22 if there appears the name "Custudio" (as in Cell A27) then all the cells in the range A2 - Q22 should change to yellow if the name appears. I would like to repeat this for different colours with all the Cells in the match day list.

When i change the name in A27.... for example to "Cuņat" then i would need all the cells with Cuņat to appear in yellow


in the FOLLOWING MATCHES I will need to copy and past the next teams information into the excel sheet and be able to quickly type into the matchday list to get the same conditional formatting.

Select all the cells in the range A2:R22 then click on Conditional Formatting | New Rule | Use a formula... , and then put this formula in the dialogue box which pops up:

=ISNUMBER(SEARCH($A$27,A2))

Note that you will probably need to use a semicolon ( ; ) instead of the comma ( , ), and you will need to translate the function names, depending on your regional settings.

Click on the Format button | Fill tab and choose yellow, then OK your way out. Excel will automatically adjust the A2 cell reference to suit all the cells that had been selected.

You can use the Format Painter icon to apply the formatting from column R into the next column when you need it.

Hope this helps.

Pete

=SEARCH($A$27,A2) is enough for CF because ERROR is not TRUE but any numbers is true by default.