ANALYSTBANK
Board Regular
- Joined
- Aug 16, 2013
- Messages
- 58
Dear Valued fellow members,
I've a worksheet having only dates and want conditional formatting. Here I lay down the steps involved, I do not wish to go with conventional conditional formatting, but a macro which can do my job. Kindly guide.
1 Range to be evaluated D3:F20 - This can be kept as used range, starting from column C, Row 3 untill end i.e F20
2 Starting with 'Column C' taking as base column,
Each cell in Column C should be the Base for evaluation against similar dates to be found in a manner, explained below, in subsequent columns, say D-F
3 For, Cell C3 (formula based value in this cell), which is the starting cell, macro should copy the date value, and put in Cell H3. Cell H4:H9 is formula driven and calcuates, 3 days forward, and 3 days backward. (this is to avoid creating variables withing macro, and ease process, i guess)
4 Date value in Range H3:H9, should be within the range of =Today() and someday forward, which is set in cell K2, currently set as 45 days from Today
5 So, If date value in H3:H9 is within the range i.e. >= Today (set in K1), AND ALSO not beyond 45 days from now i.e. <=K2, then
6 Each date value in Cell Range H3:H9 (with above condition) should be compared against each cell starting with Column D, then Column E, and then Column F and so on, and where exact match (Exact date) is found in Column D-F, the respective date should be highlighted
7 Once above is done, Cell C4, should be evaluated, i.e. Date value in C4 should be kept in H3, which will change (thru formula) dates in H4:H9 - days forward and backward
8 Now this revised range H3:H9 with condition of date within the range as given in 5 above should be repeated,
9 Same as 6 above
10 And so on for each cell range (used row range) in Column C (C5, then C6, then C7, until blank row is hit in column C) against all subsequent columns D-F
In practice, I would have row over 100+ in bottom, and Column across upto M or O to the right
My worksheet looks like as under - link - http://imgur.com/iyz8J8m or http://i.imgur.com/iyz8J8m.jpg
I've a worksheet having only dates and want conditional formatting. Here I lay down the steps involved, I do not wish to go with conventional conditional formatting, but a macro which can do my job. Kindly guide.
1 Range to be evaluated D3:F20 - This can be kept as used range, starting from column C, Row 3 untill end i.e F20
2 Starting with 'Column C' taking as base column,
Each cell in Column C should be the Base for evaluation against similar dates to be found in a manner, explained below, in subsequent columns, say D-F
3 For, Cell C3 (formula based value in this cell), which is the starting cell, macro should copy the date value, and put in Cell H3. Cell H4:H9 is formula driven and calcuates, 3 days forward, and 3 days backward. (this is to avoid creating variables withing macro, and ease process, i guess)
4 Date value in Range H3:H9, should be within the range of =Today() and someday forward, which is set in cell K2, currently set as 45 days from Today
5 So, If date value in H3:H9 is within the range i.e. >= Today (set in K1), AND ALSO not beyond 45 days from now i.e. <=K2, then
6 Each date value in Cell Range H3:H9 (with above condition) should be compared against each cell starting with Column D, then Column E, and then Column F and so on, and where exact match (Exact date) is found in Column D-F, the respective date should be highlighted
7 Once above is done, Cell C4, should be evaluated, i.e. Date value in C4 should be kept in H3, which will change (thru formula) dates in H4:H9 - days forward and backward
8 Now this revised range H3:H9 with condition of date within the range as given in 5 above should be repeated,
9 Same as 6 above
10 And so on for each cell range (used row range) in Column C (C5, then C6, then C7, until blank row is hit in column C) against all subsequent columns D-F
In practice, I would have row over 100+ in bottom, and Column across upto M or O to the right
My worksheet looks like as under - link - http://imgur.com/iyz8J8m or http://i.imgur.com/iyz8J8m.jpg
Last edited:
