Jul 30 2019 06:29 AM - last edited on Nov 09 2023 11:09 AM by
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!
Sep 03 2023 02:08 AM
Let's say you want to format rows from row 2 to row 100 that contain "sales" with possibly other text in column D.
Select rows 2 to 100.
The active cell in the selection should be in row 2, for example A2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(SEARCH("sales", $D2))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Sep 20 2023 11:12 AM
Oct 30 2023 07:56 AM
Oct 30 2023 08:42 AM
Select B1:J800. The active cell in the selection should be in row 1.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(SEARCH("Text",$C1))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Nov 16 2023 06:11 AM
Dec 04 2023 05:42 AM - edited Dec 04 2023 05:44 AM
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.
Dec 04 2023 06:52 AM
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.
Dec 20 2023 06:34 AM
Jan 02 2024 07:58 AM
Hi Sergei,
I am drawing blanks on a current issue I am having with the excel formatting (see snippet below). The table on the left side has over 4500 rows of hazardous material detail. I utilized a XLOOKUP formula for single row of data to automatically fill in the respective row on the large table by UN number. Is there a way to have this row automatically appear at the top so you don't have to scroll down. I highlighted the PSN, but would still have the row get filtered to the top. I hope I got the message across. Thank you!
Jan 12 2024 11:43 AM
Take a look at this 5 minute video instead of searching this page for half an hour :
https://youtu.be/XHT4paRaY4g Excel Conditional Formatting with Formula | How to Get it RIGHT Every Time
Jan 24 2024 02:43 PM
I need to be able to highlight A4 if E4:Q4 does not contain "R". I know how to do it for finding a value but not for not finding it. I have tried putting NOT in front of my formula but did not work.
Jan 24 2024 02:46 PM
Jan 27 2024 06:17 AM
Feb 20 2024 04:47 AM
@AUSTXCHICK thank you so much. saved me a lot of aggro!
Feb 20 2024 04:48 AM
Feb 26 2024 12:57 PM
Mar 06 2024 02:23 PM
Mar 07 2024 12:37 AM
Mar 07 2024 07:16 AM
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