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
May 28 2021 02:28 AM
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?
Jul 19 2021 02:12 AM
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.