Jul 30 2019 06:29 AM
Jul 30 2019 06:29 AM
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!
Jul 30 2019 06:58 AM
You may create the rule with the formula
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.
Jul 30 2019 07:21 AM
Thank you for your response @Sergei Baklan
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!
Jul 30 2019 08:47 AMSolution
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
Jul 30 2019 10:39 AM
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!
Jul 30 2019 01:56 PM
for the rule formula you may use
Aug 29 2019 10:13 AM
Aug 29 2019 01:47 PM - edited Aug 29 2019 01:48 PM
Yes, use exactly the same formula for the rule, and apply it to the range $B$1:$N$1000 or so.
Nov 12 2020 07:21 AM
@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)
Dec 31 2020 12:22 AM
Jan 02 2021 03:04 AM
1) unmerge cells in column I
2) apply to it conditional formatting rule with formula like
assuming your range starts from the cell I2
Jan 29 2021 06:33 AM
Jan 29 2021 12:58 PM
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