Highlight a row in excel mac 2011 when a cell has a specific word

Question

In Excel 2011 Mac (this seems to be important since the other reference solutions in stackoverflow for Excel or older versions no longer work).

I want to apply conditional formatting, so when the cell in column D includes the word student, the complete row that includes the cell gets the color format (blue for white / blank text for the cell).

I tried INDIRECT and some other formulas, but I do not understand. Only the cell containing the word receives formatting, and not the entire row (that is, the remaining cells in the same row where the pattern matches).

Answer 1

Admittedly, this answer is based on the Windows version, but it should work for you anyway.
(Pictures taken from a combination of versions of Windows and Mac, where possible.)

• Select Manage Rules ... on the Conditional Formatting menu.

  

• Click the New Rule ... button.

  

• Select Use Formula to determine which cells should be formatted , enter the formula as shown below, and click the Format ... button to select a conditional format (blue text without fill).
  - You said that you searched for the word “student” in column D, and I suggested that line 3 is the first line in which you want to apply this conditional formatting. Just change 3 to a different line number if it is not.

  

  If the word "student" is not the only one in your target cell, use the following formula instead: =ISNUMBER(SEARCH("student",$D3))

• Then enter the range in the Applies to text box, as shown.
  - In this example, we assume that line 3 is the first line, and line 400 is the last line to which you want to apply conditional formatting.
  - Please note that we did not include columns in the formula, since we want each column of each row to be included.

  

• Click OK and you should do.

I hope this works for you.

Answer 2

Follow these steps for conditional formatting.

Step 1 - Select Conditional Formatting > New Rule ...

Step 2 - Select a Style .

Step 3 - Choose Classic

Step 4 - select the drop-down list with Only for top or bottom ranking only

Step 5 - Choose from the drop-down list Use the formula to determine which cells should be formatted

Step 6 - select the desired cells and pay attention to the current cell. (D3)

Step 7 - Use the formula: = MATCH ("Student", D3.0) in the formula section.
Again, D3 is the current cell.

Step 8 - Click OK