How to change cell colour based on numer of months?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Back
Top