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 20, 2023

SergeiBaklan Yes, thanks HansVogelaar your advice worked! I took of the $ from the formula source

Thanks all

  • Smllchng5's avatar
    Smllchng5
    Copper Contributor
    Aug 16, 2023

    Charlie585  Hi there. I am hoping you can help. I have an Attendance Spread sheet and my cells have a Data Validation list on Columns C4 to CD100, and there are Conditional formats for that. But what I want to do is to have it so that, If we also have one (In any cell from C4 to CD100 that show "Attrition" (Where they leave the company) it will go over to column A and Cross out the name and color the box in Grey. I can do it for 1 column, but format painter or Copy> Paste Special will not copy the formating over. Any tips. Here is a screen shot with fake names. I have tried to change the range to C4:CD4  and C4:CD100, and nothing seems to make a difference. 🙂 Thanks for any help you can give. 

     

    • Smllchng5's avatar
      Smllchng5
      Copper Contributor
      Aug 16, 2023
      I also changed it to A4:CD100, and that didn't make a difference. I have tried with $ before the column and rows. Nothing seems to give me what I am hoping for. I appreciate the help. 🙂
      • Smllchng5's avatar
        Smllchng5
        Copper Contributor
        Aug 16, 2023

        I applied that formula....But if I marked Attrition in C56, then all the A cells above are crossed out (A54, A55, & A56 all were crossed off.) I only wanted A56 to be crossed out. Can that be done without wrtting a new format for each column? Thanks so much. I appreciate your help. 🙂

Resources