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
I applied that formula....But if I marked Attrition in C56, then all the A cells above are crossed out (A54, A55, & A56 all were crossed off.) I only wanted A56 to be crossed out. Can that be done without wrtting a new format for each column? Thanks so much. I appreciate your help. 🙂
Select A4:A100 or further down if you wish.
A4 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > Manage Rules...
Delete all existing rules.
Click New Rule...'
Select 'Use a formula to determine which cells to format'.
Enter the formula=OR(C4:D4="Attrition")
Click Format...
Activate the Font tab.
Select Italic, and tick the Strikethrough check box.
Activate the Fill tab.
Select a shade of gray as highlight color.
Click OK until all dialogs have closed.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.