One of the Microsoft Excel features I use quite a bit is Conditional Formatting. This is the feature (introduced in Excel 2007) that lets you re-colour a cell in your worksheet depending on the criteria you specify, e.g. highlight any cells containing the word “Server 2003” in red.
The problem I was having was that I wanted the whole row to be highlighted, not just the particular record. It turns out this is fairly easy to do, even though it looks a bit difficult.
Step 1 – Create a new rule
- The easiest way to start is to select one cell containing the text you want to highlight
- Click the conditional formatting button on the toolbar and go to Highlight Cell Rules > Text that contains…
- Format the text how you like, e.g. Light Red Fill with Dark Red Text
- Click OK
You should now have one cell in your spreadsheet that is formatted how you want
Step 2 – Apply rule to the whole table
- Click the conditional formatting button on the toolbar and go to Manage Rules…
- You will see your new rule listed but the Applies to box will only reference one cell e.g. ‘ =$B$2 ‘
- Change the text in the Applies to box to refer to the whole table e.g. ‘ =$A$1:$H$100 ‘
- Click the Apply button
Now that rule will highlight any matching text in the entire table, not just one cell Continue reading