Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Conditional formatting, Days outstanding count
Hi
I'm trying to apply conditional formatting (shade a range of 4 colums x 25 lines) based on the days an order took to be delivered. - If DateOrdered to DateDelivered took longer than 30 days (or is still outstanding), shade red. - If DateOrdered to DateDelivered took shorter than 30 days, shade green. I've tried recording a macro for some guidance, but have difficulty with the calculation first part. Tried adding a "calculate days outstanding" column, but then get #Value! error when order is not yet delivered. Would appreciate some guidance. I'm still reading Walkenbach's VBA for Dummies... Thanks a lot. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Conditional formatting, Days outstanding count
I am assuming the dates are in column A and B, and the rest is in C and D
Select A1:D25 Goto FormatConditional Formatting Change Condition1 to Formula Is Add a formula of =$B1-$A130 Click Format, select the Pattern tab, and select Red OK Click Add Change Condition2 to Formula Is Add a formula of =$B1-$A1<=30 Click Format, select the Pattern tab, and select Green OK OK -- HTH Bob Phillips "AdP" wrote in message ... Hi I'm trying to apply conditional formatting (shade a range of 4 colums x 25 lines) based on the days an order took to be delivered. - If DateOrdered to DateDelivered took longer than 30 days (or is still outstanding), shade red. - If DateOrdered to DateDelivered took shorter than 30 days, shade green. I've tried recording a macro for some guidance, but have difficulty with the calculation first part. Tried adding a "calculate days outstanding" column, but then get #Value! error when order is not yet delivered. Would appreciate some guidance. I'm still reading Walkenbach's VBA for Dummies... Thanks a lot. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Conditional formatting, Days outstanding count
if you need to shade more than 1 cell on the same condition which for your "records" would be the case I usually insert an extra column with a formula. Then in the format conditions i simply point to the (same) formula. Easier to make and to maintain. Formula could be something like.. =IF(DateDelivered-DateOrdered=<30,0,IF(ISBLANK(dateDelivered),1,2)) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam AdP wrote : Hi I'm trying to apply conditional formatting (shade a range of 4 colums x 25 lines) based on the days an order took to be delivered. - If DateOrdered to DateDelivered took longer than 30 days (or is still outstanding), shade red. - If DateOrdered to DateDelivered took shorter than 30 days, shade green. I've tried recording a macro for some guidance, but have difficulty with the calculation first part. Tried adding a "calculate days outstanding" column, but then get #Value! error when order is not yet delivered. Would appreciate some guidance. I'm still reading Walkenbach's VBA for Dummies... Thanks a lot. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Conditional formatting, Days outstanding count
Thanks a lot. I was unnecessary thinking too complex.
Anton. "Bob Phillips" wrote: I am assuming the dates are in column A and B, and the rest is in C and D Select A1:D25 Goto FormatConditional Formatting Change Condition1 to Formula Is Add a formula of =$B1-$A130 Click Format, select the Pattern tab, and select Red OK Click Add Change Condition2 to Formula Is Add a formula of =$B1-$A1<=30 Click Format, select the Pattern tab, and select Green OK OK -- HTH Bob Phillips "AdP" wrote in message ... Hi I'm trying to apply conditional formatting (shade a range of 4 colums x 25 lines) based on the days an order took to be delivered. - If DateOrdered to DateDelivered took longer than 30 days (or is still outstanding), shade red. - If DateOrdered to DateDelivered took shorter than 30 days, shade green. I've tried recording a macro for some guidance, but have difficulty with the calculation first part. Tried adding a "calculate days outstanding" column, but then get #Value! error when order is not yet delivered. Would appreciate some guidance. I'm still reading Walkenbach's VBA for Dummies... Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating days sales outstanding | Excel Worksheet Functions | |||
conditional formatting highlight dates 90 days or less | Excel Worksheet Functions | |||
Conditional Formatting Dates calculating 10 days and 30 days from a certain date | Excel Worksheet Functions | |||
Conditional formatting based on a date + 30 days | Excel Discussion (Misc queries) | |||
count conditional days - ajit | Excel Discussion (Misc queries) |