Forum Discussion

AUSTXCHICK's avatar
AUSTXCHICK
Copper Contributor
Jul 30, 2019
Solved

Conditional formatting for entire row based on data in one cell

I need all cells in a row to highlight a certain color if the data in one cell contains a specific word.    What I specifically want is for an entire row to turn grey if the status cell contains th...
excel
Formulas and Functions
  • Yury Tokarev's avatar
    Yury Tokarev
    Jul 30, 2019

    Hello

     

    Assuming that D is the column containing the status cells, and you wish to apply conditional formatting to rows 1:50, here are the steps:

     

    1.  Select any cell in row 1

    2. Go to 'Conditional Formatting>New Rule>Use a formula to determine which cells to format'

    3. In the formula field paste =$D1="Shipped", set the required format and click 'OK'

    4. Go to 'Conditional Formatting>Manage Rules' and locate the rule you have just created

    5. In the field 'Applies to' paste '=$1:$50', or just select the required rows, starting from row 1, then click 'OK'.

     

    If you type the word 'SHIPPED' in any cell of the column D (rows 1 to 50), the entire row will be conditionally formatted

     

    Hope this helps

    Yury

     

AVE_123's avatar
AVE_123
Copper Contributor
Apr 03, 2023

Yury Tokarev Hello, does this work in Excel online also?

Yury Tokarev's avatar
Yury Tokarev
Iron Contributor
Apr 03, 2023
Yes it does. The interface has been improved, and it is now possible to setup the conditional formattting in one place. Here are the steps:
1. Select Home>Styles>Conditional Formatting>New Rule
2. Enter $1:$50 into the box 'Apply to range"
3. Select 'Formula' in the 'Rule Type drop-down' box
4. Enter $D1="Shipped" in the field 'Format all values where this formula is true'
5. Select the desired formatting option or a custom format and click 'Done'.

Resources