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
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
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
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
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
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
