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

     

SergeiBaklan's avatar
SergeiBaklan
MVP
Jan 29, 2021

kwoods0392 

Could you please illustrate the question with small sample file - it's not clear where is the number, where are values as shipped, do you use range or table

elgar65's avatar
elgar65
Copper Contributor
May 28, 2021

SergeiBaklan 

 

Hello Sergei, it seems like you have many helpful answers, hopefully you can help me too.

 

My situation is as follows:

 

A sheet with in column A the dates, a date may or may not occur more then once. In column B names, in column C a start time, D end time, etc (it is basically a calendar).

 

I want the first instance of each date to have a line above it, so each day gets separated by a line, making the sheet more readable. I want this line to extend over the entire row, or at least the once with data in it.

 

I use the formula countif($A$1:A1,A1)=1 to find the first instance of each date. This works perfectly, but only for column A. When I apply this  formula to my range, the formatting will look at each column separately, and put the line above each first occurrence of every value, instead of extending the line from column A. Is there a way around this?

 

Thanks,

Elgar

Resources