In MS Excel, conditional formatting allows you to format a cell depending on its value or as soon as its value is changed. For instance, if someone changes the content in the cell to Yes, you can make the entire cell green via this dynamic feature. Now, this is just about changing the format of cells.
However, what if you desire to highlight the entire row via conditional formatting? Well, you will be happy to know that MS Excel 2007 has the ability to facilitate even such formatting. Let us check this out now!
Problem: Need To Highlight Final Status Row
Let us assume that you are reviewing a document in your office and are entering all the errors in an Excel 2007 spreadsheet. This is the standard review sheet that you have created for your team to log errors. It has columns namely Sr. No, Author, Error, and Status. Now, the cells in the Status column have a drop-down icon for selecting Open, Pending or Fixed. You as the reviewer have to select Open, while the author needs to select Pending if the error is not rectified or Fixed if the error is removed.
After fixing all the errors, the author has to enter All and Done in Error and Status columns respectively at the end, which is known as the Final Status row. To know whether all the errors have been fixed or not, you want to highlight this row.
Solution: Use Conditional Formatting
In the above scenario, it is only the conditional formatting feature of MS Excel that can aid you in highlighting the entire row when the Final Status is specified as Done. Unfortunately, not many of us are aware of the fact that this feature can even highlight the entire row as well.
Implement The Solution: Select The Entire Row After Applying The Formatting To A Specific Cell
You need to first set the formatting for the specific cell and then select the entire row to apply the formatting. Here are the steps to highlight a row in MS Excel 2007.
1. Select the cell to which you want to apply conditional formatting.
2. In the Home tab, select Conditional Formatting –> Manage Rules. This will display the Conditional Formatting Rules Manager dialog box.
3. Click the New Rule button.
This will display the New Formatting Rule dialog box.
4. From Select a Rule Type list, select the Use a formula to determine which cells to format option (the last one). The Edit the Rule Description section is displayed.
5. Enter the formula in the text box. For example, considering the above scenario, you need to enter =$D$30=”Done” where D30 is the cell address (D as the column and 30 as row) and Done is the value or condition based on which you want to highlight the row. This formula will hold true whenever cell D30 will contain the word Done in it.
6. Click the Format button. This will display the Format Cells dialog box.
7. Select the Fill tab.
8. Select the suitable color.
9. Click OK. You can see the cell preview in the Preview section.
10. Click OK again to return to the manager.
11. Click Apply. Now, you should be able to see the changes in the cell. If the formatting does not change, you will have to keep changing your formula.
12. Click the button at the end of the Applies to field. The Conditional Formatting Rules Manager shrinks to show only the Address field.
13. Drag the selection across the row.
14. Click the button besides the Address field. You return to the full dialog box, observe a marquee around the selection, and note that the Applies to field shows the range of cells selected by you.
15. Click Apply. You will see that the entire row is highlighted as per the new formatting rule.