How to change cell colour based on numer of months?

[Guest]

Hello all,

I need a function that have an initial date on a cell (ex: 25-05-2005) and
returns one year next to that date (ex:25-05-2006), and this is ok.

To this date that i have(25-05-2006) i need to count the number of months
that passed until now(in this case is 0 months) and if the month is 0 to 10,
fill in the current row the (25-05-2006) cell to red. If is in 11 to 12 fill
it in green. and if is it higher than 12, fill in yellow

my problem is, that the excelsheet does', select any cell and don't fill
anything

can you help me?
Thank You

PS: i'm trying in that way

Public Function give_date(initialdate, line)
......

Select Case numerofmonths
Case Is <= 10
Range("G" + Trim(Str(line))).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Case 11 To 15
Range("G" + Trim(Str(line))).Select
With Selection.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With

Case Is > 15
Range("G" + Trim(Str(line))).Select
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With

End Select

give_date = nextdate

End Function

 

[Ivan Raiminius]

Hi,

can't you use conditional formatting?

Regards,
Ivan

 

[Guest]

and put what?

 

[Ivan Raiminius]

Hi,

formula like:

=datedif(date_you_have,now(),"m")<=10

and the others two (believe you can write them yourself)

date_you_have must be earlier then now.

Regards,
Ivan

 

[Bob Phillips]

Time for some research. Check out See
http://www.contextures.com/xlCondFormat01.html

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

it's an ideia,

but it's better on a function, my big question, is why in the function, the
color of worksheet doesn't change.

i want to change the color tab too, and in conditional formating i can't

thnks ivan

 

[Ivan Raiminius]

Hi Tiago,

it is not possible to change color of a cell via function (why is it
better with function?). Use sub instead. Best solution is probably
conditional formatting.

Right, you cannot change tab color using conditional formatting. You
can use statement like this:
worksheets("somesheet").Tab.ColorIndex = some_number_(vba_constants)

Regards,
Ivan