98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting
Conditional formatting
Resolved · Low Priority · Version 2007
Mon 7 Jul 2014, 15:38Delegate Jay said...
Jay has attended:
Excel Intermediate course
Excel Advanced course
Conditional formatting
How do you highlight a whole row using conditional formatting.
E.G
if i wanted the row to be highlight is the date was 1 week away
regards
Jay
Mon 7 Jul 2014, 16:53Trainer Doug said...
RE: Conditional formatting
Hi again Jay
If you want to colour a whole row by conditional formatting where a date is one week away start by highlighting all the rows of data (not the header row) then
Select Home, Conditional Formatting
Click on the option 'Use formula to determin which cel to format'
Now enter the formula.
Suppose your table contains a Start date in the D column and the table starts in A1 the formula would be:
=$D2<=TODAY()+7
Click the Format button to define the type of formatting to add then press OK.
Only rows which are within 1 week of the Start Date will be coloured.
Editing the Rule
If you need to edit or change the rule for example to 2 weeks away then highlight the same range of cells and choose Conditional Formatting, Manage Rules, Edit.
Regards
Doug
Best STL
Mon 7 Jul 2014, 19:39Delegate Jay said...
RE: Conditional formatting
Hi
this did not seem to work, it seemed to highlight everything,So Row A are my Headers and Column I is where my dates are.
Regards
Jay
Tue 8 Jul 2014, 12:06Trainer Doug said...
RE: Conditional formatting
Hi Jay
If it's highlighting everything there may be too many $ signs in the formula. I think it should be
=$I2<=TODAY()+7
The idea is rows with dates more than a week away should not get highlighted.
Doug
Tue 8 Jul 2014, 12:37Delegate Jay said...
RE: Conditional formatting
OK brilliant ive done that works, however i want to copy down to cells 1000 so that when i add new ones in they are effected by teh colour... (i only have 100 rows at tehe moment) however all the blanks are coming up with the color can we do like IF (BLANK) ignore?
regards
Jay
Tue 8 Jul 2014, 16:53Trainer Doug said...
RE: Conditional formatting
Hi Jay.
Glad the rule worked.
If you want to ignore blanks change the formula to
=AND($I2<=TODAY()+7,$I2<>"")
Then you can extend the range to be formatted.
Good question :)
Doug
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Freeze Rows and Columns to keep lables displayedYou can freeze rows and columns in your worksheet so they don't move. |
