Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining conditional formatting and functions
Hi all,
I want an empty given cell, say AA5, to become the color red when a range of 8 cells in the same row starting 13 columns to the left of AA5 (i.e. the range is N5:U5) is blank. (To explain, if a payment was not made over the 8 recent months, I want to get the automatic red "alarm".) This probably involves: - conditional formatting of AA5 (to get the red color) - the IF function (IF the range is blank) - the OFFSET function (OFFSET(AA5, 0,-13,1,8) Each month I insert a new column for the new month, making the given cell AA5 become AB5, but the range (8 cells in the same row starting 13 columns to the left) stays the same. That is why I think the OFFSET function should be used. I cannot manage to put it all together and make it work. Any help appreciated. Thanks. Zvi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining conditional formatting and functions
Use conditional formatting with that formula
=COUNTA(OFFSET($AA$5,0,-13,1,8))=0 When in C F, change Condition 1 to Formula Is -- HTH Bob Phillips (remove nothere from email address if mailing direct) "zvi" wrote in message oups.com... Hi all, I want an empty given cell, say AA5, to become the color red when a range of 8 cells in the same row starting 13 columns to the left of AA5 (i.e. the range is N5:U5) is blank. (To explain, if a payment was not made over the 8 recent months, I want to get the automatic red "alarm".) This probably involves: - conditional formatting of AA5 (to get the red color) - the IF function (IF the range is blank) - the OFFSET function (OFFSET(AA5, 0,-13,1,8) Each month I insert a new column for the new month, making the given cell AA5 become AB5, but the range (8 cells in the same row starting 13 columns to the left) stays the same. That is why I think the OFFSET function should be used. I cannot manage to put it all together and make it work. Any help appreciated. Thanks. Zvi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combining conditional formatting and functions
Bob,
Your solution looks good. Thanks, Zvi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions | |||
Nested functions in conditional formatting formulae | Excel Discussion (Misc queries) | |||
Multiple FIND functions in Conditional Formatting | Excel Worksheet Functions | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) | |||
Conditional Formatting | Excel Worksheet Functions |