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
HansVogelaar That worked beautifully. I see what I was doing wrong now. Thank you. One more thing. Here is the situation. I enter the names on Worksheet 1, then I did an IF statment to have it automatically enter those names on worksheet 2. Now that I have Worksheet 2 that may have that "Attrition" condition because of the conditional formatting (1st screenshot below).... Is it now possible to take that formatting and apply it to Worksheet 1 so it will automatically show which names have the Strikethrough and highlight? (like is shown in second screenshoot....I did it manually to show you what I want) I tried format painter, but that didn't work. Thanks so much. You are a life saver. 🙂
Let's say the agent names are in A2:A100 on the Agent Contact Info sheet.
Select this range. A2 should be the active cell in the selection.
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
=OR(XLOOKUP(A2, Attendance!$A:$A, Attendance!$C:$D, "")="Attrition")
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Thanks for your private message.
I found the cause of the problem: the sheet is named "Attendance " with a space at the end. So the formula should be
=OR('Attendance '!C4:CD4="Attrition")
It should work! Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
- Yes it is. That is what is throughing me off...although I do have other workbooks linked to the Agent Contact info. Is that why? If I can't do it...I can do it manually...just thought I would try. Thanks for your help. You have been great.
But isn't the Attendance sheet in the same workbook as the Agent Contact Info sheet?
- Thank you. I appreciate the help. You are amazing 🙂 Sadly, I must have an order version or something because I get an error that says "You may not use references to another workbooks for Conditional Formatting Criteria.". It is okay. I can do it manually...just thought it would be nice to have it be automatic...but at least it is on the other sheet. Thank you so much. I appreciate your help