Forum Discussion
AUSTXCHICK
Jul 30, 2019Copper Contributor
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...
- 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
Lady_Di
Aug 29, 2019Copper Contributor
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?
SergeiBaklan
Aug 29, 2019MVP
Yes, use exactly the same formula for the rule, and apply it to the range $B$1:$N$1000 or so.
- dionne103Mar 07, 2024Copper ContributorHi Sergei! I used this to highlight the entire row if the columns from B to CP contain the specific texts I'm looking for. I also wanted to use the "AND" function as I'm looking for more than 1 text. But this didn't work for me. Is there another formula for this? Thanks!
- SergeiBaklanMar 07, 2024MVP
For such range
if you'd like to highlight rows with BOTH abc and xyz conditional formatting rule formula could be
=SUMPRODUCT( ($B2:$H2="abc") + ($B2:$H2="xyz") ) >= 2
, as
- BathindaHelperDec 04, 2023Copper Contributor
Regarding your reply/solution at 1348 hrs on 29 Aug, 2019, here, ::
This didn't work. I wanted to highlight b1:e1 when there is some specific text in a1. I've typed this conditional formula: =A1="some_text" and applied it to this: =$B$1:$E$1000. But when there is desired value in a1, only b1 is highlighted. I've tried many tries. Thanks again.
- HansVogelaarDec 04, 2023MVP
Change the formula to
=$A1="some text"
The $ before the column letter A fixates the column, so that B1, C1, D1 and E1 all look at A1.
- BathindaHelperJan 27, 2024Copper ContributorThank you very much. Particularly the last sentence of your answer that explained the reason as well. Thanks again. You people are really great.
- Thom_BeschThomBeschDec 14, 2022Copper Contributor
Sergei,
Is there a way to change the entire row color if the text is the same in one cell, then to change the a different entire row(s) when the text changes to another word to a different random color?
I want to have an entire spread sheet become color coded based on a program name in column F. I don’t care what the colors are.
thank you,Thom
- HansVogelaarDec 14, 2022MVP
You'd have to create a different rule for each word.
For example, if you want to color a row if column F contains Excel:
- Select all cells. The active cell in the selection should be in row 1.
- On the Home tab of the ribbon, select Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula =$F1="Excel"
- Click Format...
- Activate the Fill tab.
- Select a color.
- Click OK, then click OK again.
- Repeat for other words.
- Stammer410Mar 15, 2024Copper ContributorI am ecstatic. This is a game changer for me. Thank you!
- Lady_DiSep 04, 2019Copper ContributorWorked perfectly. Thank you!