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.

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.

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.

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.