Forum Discussion

AUSTXCHICK's avatar
AUSTXCHICK
Copper Contributor
Jul 30, 2019

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

     

Charlie585's avatar
Charlie585
Copper Contributor
Apr 19, 2023

SergeiBaklan I hope you can help me, I have created a conditional rule eg. if A1 is higher in value than B1 it should highlight green but if its lower than it should highlight red, I know want to drag it down across the entire sheet but what's happening is that all formulas are based on the same B1 instead I need each row of A to compare to the same row in B (so A5 compares to B5), I have tried adding :$A$700 but that didn't work either - Please help

Resources