Conditional Formatting

[twmoore]

Help please. I am trying to highlight all cells in a large spreadsheet that contain the same certain text value. For example all cells contain text such as "B6 - A5", or "C7 - B4" etc. (These are not cell references, they are softball team designations) I am trying to highlight all cells that contain for example the text "B6". Nothing I've tried seems to work.

 

[Aladin Akyurek]

Is that a single text value or a list of text values? In which range exactly do you want to apply conditional formatting?

 

[twmoore]

Thanks for your response Aladin. Sorry for the delay - I've been out of town. To answer your question: Single text entries in the range B5:J86. Each cell in the range will have different text entries such as A4-A6, or C5-C1, or B4-B7. I am looking to color shade (for example) all cells in the range that contain text "B4". Hope this helps! Cheers.

 

[Barry Katcher]

You can use conditional formatting. Highlight the range containing all your combinations, as an example, range A1:A50. Click on Format/Conditional Format and choose Formula is: and enter this formula:

=FIND("B4",A1)>0

and select the formatting you want.

 

[Aladin Akyurek]

Thanks for your response Aladin. Sorry for the delay - I've been out of town. To answer your question: Single text entries in the range B5:J86. Each cell in the range will have different text entries such as A4-A6, or C5-C1, or B4-B7. I am looking to color shade (for example) all cells in the range that contain text "B4". Hope this helps! Cheers.

Enter in cell B4 the team designator of interest like B4.
Select B5:J86, the range of interest.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter as formula:

=SEARCH($B$4,B5)

Activate the Format button.
Choose the desired formatting using one or more tabs (Font, Border, Patterns).
Click OK, OK.

You might even set up a dropdown list of teams in cell B4 using data validation.

 

[Barry Katcher]

Much better, Aladin. I tried that with FIND but it only accepts text as the first argument. Didn't think of SEARCH.

 

[Barry Katcher]

Aladin, I just tried to use your SEARCH in a recorded macro to use the Edit/Find menu option to find and highlight each occurrence of the text entered in the cell (cell B4 in your example).

I tried using a SEARCH formula in the "Find what" box in the "Find" drop-down box, but it doesn't like cell references or formulas any more than the "Find" function did. Is there any way to get the "Find" menu function to use cell references instead of text?

 

[Aladin Akyurek]

I think you need to use the VBA equivalent of the Search or Find worksheet function in your macro, not the Edit/Find route.

 

[Barry Katcher]

Thanks, Aladin. I'll check out VBA Help and see if I can do it.

 

[amazingd]

Set the conditions as follows: use formula, then the following condition for cell B2 (assuming that is one of the cells)
=find("C6",B2)=1 then set condition. Set another condition
=find("C6",B2)=4 then set condition (the 4 assumes no spaces in text. if there are spaces before and after the - then use 6. If the place of C6 indicates the home team, you could use a different highlight for it.

This format can then be copied to all cells by doubleclicking the paintbursh and highlighting all the cells involved.

You could add flexibility by having the text "c6" in a cell and using the cell address in the condition. that way, you could change the highlighting to any team desired by changing the contents of the singel cell.