Automating calculations/formatting

[EliseFreedman]

I would now like to be able to use VBA to automate some calculations and formatting on my spreadsheet

I would like to be able to work through every row on the spreadsheet and calculate the number of days elapsed based on dates in two columns. I would then like to read the values in the days elapsed column and place different values in another column depending on the value in the days elapsed column. The TLS column should have different values depending on the number of days elapsed for example if <30 days have elapsed, then TLS should be 2, if exactly 30 days have elapsed TLS should be 3 and if > than 30 days have elapsed TLS should be 4

My spreadsheet is as follows

Date Received Todays Date Days Elapsed TLS
01/03/2006 27/04/2006 57
01/04/2006 27/04/2006 26
28/03/2006 27/04/2006 30

Any ideas on how i should do this would be appreciated

 

[xlbo]

you could do this very easily with a formula and a lookup table - is there any reason this needs to be in VBA ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

A starting point:
Dim r As Long, e As Integer
For r = 2 To Cells(2, 1).End(xlDown).Row
e = Cells(r, 2) - Cells(r, 1)
Cells(r, 3) = e
Cells(r, 4) = IIf(e < 30, 2, IIf(e = 30, 3, 4))
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EliseFreedman]

I think I will have to use VBA to get it formatted the way i want because in addition to picking up the fact that if it is < 30 days it should have a value of 2 etc, i also wanted to read the value in the TLS column and format the text and cell differently depending on the value for example if it is 2 then format the whole row in green text and the Traffic Light System Column(Column next to TLS) should be shaded Green etc.

 

[xlbo]

This can all be done in formulae and conditional formatting.....

Set up a data table like:

0 2
30 3
31 4

name the range "TLS_Lkup"
formula in TLS column would be:
=VLOOKUP(C1,TLS_Lkup,2,TRUE)

where your 'days elapsed' column is C

can then use conditional formatting on the whole row - select the whole dataset and go into conditional formatting. Change the 1st option to "Formula Is" and use:
=$D2=2
set the format colour et voila

assuming TLS in col D....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[EliseFreedman]

I have tried to get conditional formatting to work

Although I type in the formulas exactly as in your post, My formulas in the conditional formatting screen end up as follows

="$D2=2".

When I try to apply the conditional formatting, the text does not change colour

Also can I apply two different lots of conditional formatting in the one spreadsheet. The first lot being to change the colour of the text in all the rows and then the 2nd lot being to change the shading of one specific column

 

[xlbo]

have you changed the dropdown from "Cell Value Is" to "Formula Is" ??

and yes - you can have multiple sets of conditional formatting

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question