Conditional formatting for entire row based on data in one cell

[AUSTXCHICK]

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 the word "SHIPPED." I know how to make that specific cell highlight the color I want, but not the entire row of the sheet.

Thank you for your help!

[#2]

@AUSTXCHICK 

You may create the rule with the formula

=$A1="Shipped"

and apply it to your entire range, let say $A$1:$Z$200

All rows with "Shipped" will be in desired color.

A1 in formula shall be top left cell of your range.

[#3]

Thank you for your response @SergeiBaklan 

Sorry I'm a total novice, but when I followed your instructions (I think), it only highlighted the cell that contained the word "shipped" instead of the entire row. How can I make each individual row turn grey when the cell in column G for that row contains the word "shipped?"

Thank you so much!

[#4 · Accepted solution]

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

[#5]

That worked! Thank you so much!@Yury Tokarev 

[#6]

If you would allow me one more question @Yury Tokarev  -

What if, rather than the cell containing a specific word to make the row highlight, just having ANY text typed into the cell will cause the row to highlight? Is there a way to create a rule for that?

(It has been brought to my attention that there are occasionally circumstances when something has shipped, but the row shouldn't be highlighted. I want to instead program the rule to highlight when I type in the date it was received or the name of the person who signed for it, because there is no circumstance when that wouldn't necessitate the row being highlighted.)

I cannot thank you enough!

[#7]

@AUSTXCHICK 

Instead of 

=$D1="Shipped"

for the rule formula you may use

=LEN($D1)

[#8]

I used Yuri Tokarev's directions to apply "conditional formatting for entire row based on data in one cell", but am wondering if there is a way to apply conditional formatting for a consecutive set of cells (i.e., B thru N) in a row based on data in one cell in that row?

[#9]

@Lady_Di 

Yes, use exactly the same formula for the rule, and apply it to the range $B$1:$N$1000 or so.

[#10]

Worked perfectly. Thank you!

[#11]

@Yury Tokarev I've added a search feature in my worksheet that highlight the row(s) that contain my search criteria. It's using conditional formatting to highlight the results/rows.

The problem is there are many rows, and it does not jump or go to the highlighted row. I still have to scroll continuously until I find the highlighted row. 

I followed this tutorial using the same formula (https://www.youtube.com/watch?v=2axO-aAOIls&ab_channel=askfarouk)

[#12]

Hi sergei,
How can I make the cells in column I become colored only if they have no date value in them+ there is a text in column E in the same row?

[#13]

@Adikl 

1) unmerge cells in column I

2) apply to it conditional formatting rule with formula like

=($I2="")*(LEN($E2)>0)

assuming your range starts from the cell I2

[#14]

Hi! This formula is great - thank you! I am trying to have an entire row shaded like this formula does, but instead of it being shaded when a certain value is entered (such as shipped), I want it to shade the row when the number value is greater than 5. Is there a way to do this by chance?

[#15]

@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

[#16]

@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

[#17]

@elgar65 

Please use

=COUNTIF($A$1:$A1,$A1)=1

[#18]

@SergeiBaklan That worked great, thanks!

[#19]

@elgar65 , you are welcome

[#20]

@SergeiBaklan 

Hi, I want to highlight a certain similar reference numbers given for a customer, for example few customers might use the same reference number for an assessed criteria, appreciate if you could instruct me to apply conditional formatting to a single column to get the similar reference numbers to each other highlighted. (there could be few customers with similar reference numbers not just a specific one for all, I need to get such customers who might share the similar reference numbers highlighted). Thanks in advance.