Learn the solution to last week's conditional formatting stumper and see if you know the answer to our latest Word challenge.
The term highlight gets used a lot for different things. In this case, I'm referring to the colored highlight applied to text by clicking the Highlight tool on Word's Formatting toolbar. The result is similar to using a highlighting pen on a printed page. Now, assume you have highlighted text throughout the document. You might have used the same or different colors; it makes no difference in this challenge. If you wanted to change the color of all those highlights, you could select each occurrence and change the color, but doing so would be tedious, and fortunately, it's unnecessary. How would you change the color of all the existing highlights to another color at the same time?
Last week we asked…
How can you apply formatting to an entire row, based on the contents of a single cell? Fledis was the first to come up with the right response -- well... the response I had in mind! The trick to formatting the entire row instead of a single cell is to select the whole sheet before applying the conditional format. Avamreddy and Amasa also suggested expanding the range.
Let's revisit the original problem that prompted the challenge. A client was able to get conditional formatting to highlight overdue projects by the due date, but she was unable to shade the entire row (the entire project). In this case, Fledis' response does the trick. If the condition correctly identifies the right data, simply expand the range to include the entire sheet. Or more specifically, the range should include all the columns you want formatted.
This case was, in reality, a bit more complex, as the client was dealing with two conditions, not just the due date condition. As you can see, the sheet below is a simple project-tracking worksheet. The current date is April 13, 2010, and one project is overdue: AT6-02 (row 4). Let's see what happens when we attempt to highlight overdue projects using a simple conditional format based on the Due column, as my client tried.
- Select C3:C8.
- Choose Conditional Formatting from the Format menu. In Excel 2007, click the Home tab and choose Conditional Formatting in the Styles group.
- Choose Formula Is from the Condition 1 drop-down list. In Excel 2007, click New Rule and select Use A Formula To Determine Which Cells To Format.
- Enter the following formula: =$C3<Today().
- Click the Format button and then click the Patterns tab and select a color -- say, red.
- Click OK.
Excel applies the appropriate format, but it isn't right.
There's nothing wrong with the conditional formatting -- it's performing exactly as as it should. It's true that all three highlighted projects have due dates that occur before the current date. However, HR6-12 (row 3) and PR3-02 (row 8) were submitted on time. As is, this conditional format is downright deceptive!
This sheet needs a formula that also considers the Submitted value. If the current date is greater than the Due date and there's no value in the Submitted column, the project is truly overdue. Let's update the formula as follows:
- Select C3:C8.
- Choose Conditional Formatting from the Format menu. In Excel 2007, click the Home tab and choose conditional Formatting in the Styles group.
- Replace the formula with the following: =AND($C3<TODAY(), ISBLANK($D3)) and click OK.
The conditional formula pinpoints the right project now. Only AT6-02 in row 4 is truly overdue.
At this point, the conditional formatting identifies the right projects as overdue, but it highlights only the Due value. Expanding the conditional format to highlight the entire row is simple:
Do you have a conditional format that you just can't get right? If so, describe your needs and maybe someone will have just the right solution!
- Select A3:D8.
- Choose Conditional Formatting from the Format menu.
- Excel will expand the existing format to the selected range.
- Click OK and Excel highlights the entire row.