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
Charlie585 Hi there. I am hoping you can help. I have an Attendance Spread sheet and my cells have a Data Validation list on Columns C4 to CD100, and there are Conditional formats for that. But what I want to do is to have it so that, If we also have one (In any cell from C4 to CD100 that show "Attrition" (Where they leave the company) it will go over to column A and Cross out the name and color the box in Grey. I can do it for 1 column, but format painter or Copy> Paste Special will not copy the formating over. Any tips. Here is a screen shot with fake names. I have tried to change the range to C4:CD4 and C4:CD100, and nothing seems to make a difference. 🙂 Thanks for any help you can give.
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. 🙂