CODEWITHSUNDEEP
macosexcelformattingconditional-statementsrow
No Importa
No Importa

Reputation: 63

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

In Excel 2011 Mac (it seems to be important since other referenced solutions in stackoverflow for excel windows or mac older versions don't seem to work).

I want to apply conditional formatting so when a cell in column D includes a word "student" the full row which includes the cell gets a color format (blue color for the text white/empty filling for the cell).

I have tried INDIRECT and some other formulas but I don't get it right. Only the cell that includes the word gets the formatting, not the whole row (that is, the rest of the cells on the same row where the pattern matches).

Upvotes: 6

Views: 37633

Answers (2)

Billy N.
Billy N.

Reputation: 21

Follow these steps for conditional formatting.

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

Step 2 - Select Style Dropdown

Step 3 - Choose Classic

Step 4 - Select Dropdown with Format only top or bottom ranked values

Step 5 - Select from Dropdown Use a formula to determine which cells to format

Step 6 - Highlight desired Cells and take note of the current cell. (D3)

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

Step 8 - Press OK

Upvotes: -2

Iakovosian
Iakovosian

Reputation: 361

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

  1. Select Manage Rules... from the Conditional Formatting menu.

    Conditional Formatting menu

  2. Click the New Rule... button.

    Conditional Formatting Rules Manager

  3. Select Use a formula to determine which cells to format, enter the formula as shown below, and then click the Format... button to choose your conditional format (blue text with no fill).
    - You said you were looking for the word "student" in column D, and I have assumed that row 3 is the first row that you want this conditional formatting to be applied. Just change the 3 to another row number if this is not the case.

    New Formatting Rule

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

  4. Then type a range into the Applies to textbox as shown.
    - In this example, we assume that row 3 is the first row and row 400 is the last row that you want the conditional formatting to be applied to.
    - Note that we did not include column letters in the formula since we want every column of each row to be included.

    Conditional Formatting Rules Manager with New Rule

  5. Click OK and you should be done.

I hope this works for you.

Upvotes: 8

Related Questions