Forum Discussion
Solved
Conditional formatting for entire row based on data in one cell
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 th...
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
SergeiBaklan I hope you can help me, I have created a conditional rule eg. if A1 is higher in value than B1 it should highlight green but if its lower than it should highlight red, I know want to drag it down across the entire sheet but what's happening is that all formulas are based on the same B1 instead I need each row of A to compare to the same row in B (so A5 compares to B5), I have tried adding :$A$700 but that didn't work either - Please help
SergeiBaklan Yes, thanks HansVogelaar your advice worked! I took of the $ from the formula source
Thanks all
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.
Make sure that the formula refers to B1, and not to $B$1 or B$1
