Forum Discussion
Conditional formatting for entire row based on data in one cell
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
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.
Privet Sergei!
I can't get it to work for the entire row. Tried multiple times. When I apply it to the signed cell, there is no problem. When I specify a range, it doesn't work. Please help! 🙂
Thanks for the prompt response! I wanted to share my file, but can't figure out how to attach it 😞
If you cannot attach a sample file, you can make it available through OneDrive, Google Drive, Dropbox or similar
- I think this is the proper discussion thread for my question:
I need to highlight all rows that are above a cell with a specific value. For example, cell A356 contains the unique word "total" in Column A. I need rows A1:A356 highlighted. I assume it is a simple Find function in conditional formatting for the 1st rule. But I can't figure out how to highlight the range above after it finds the word. I can't use a rule specific to highlighting from rows 1 to 356 because the word changes rows for different sheets/workbooks. I would need the 2nd rule to work as part of a script where it finds the word and highlights all rows above when the range of rows is variable... Also, it can't highlight the rows below the word, so I can't just do a full column rule.
Needs to be functional as a script in Excel 365 😞
Thanks!Not sure I understood the logic. For such sample
formula for conditional formatting rule could be
=( ROW($C3) > ( XMATCH("start", $B$3:$B$12) + ROW($B$3) - 1 )) * ( ROW($C3) < ( XMATCH("total", $B$3:$B$12) + ROW($B$3) -1 ))
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.
