I am using excel 2010 and I am trying to set up a rule that will change the colour of a cell based on a date within that cell.

For example the cell may contain Site meeting - 11.05.14

I would like to be able to have it automatically change colour if it is 2 weeks away, 1 week away, 2 days away and expired.

Ideally I don't want to have a separate cell with just the date in, so possibly could the date be in the comments. I realise I am asking a lot but hopefully someone might have a solution?

I do have a solution for this but it is rough and not really what I am after. Using conditional formatting and =NOW function I can achieve the results but using two cells. I am hoping there is a way to tell excel to ignore everything in a cell before a certain symbol. In this instance "-".

Googleing has led me to VBA which unfortunately I have no idea how to use. So Hopefully someone here can help? Or at least give me a push in the right direction.

  • Dave, it doesn't have to be a worksheet function. I just though that would be the simplest way. But maybe what I am asking is something for vba? In that case I am stuffed. I have zero experience with vba. – Jonathan May 2 '14 at 10:42

This does what you want

Sub WorkOutTime()

    'see http://dmcritchie.mvps.org/excel/colors.htm for colour chart

dim columnToUse as string
columnToUse = "A"    ' update this to the colum you want to use

Dim expired As Integer
expired = 3 'red

Dim twoDays As Integer
twoDays = 8 'blue

Dim sevenDays As Integer
sevenDays = 27 ' yellow

Dim fourteenDays As Integer
fourteenDays = 7 ' purple

Dim currentCell As Integer
currentCell = 1

Do While (True)

If (Range(columnToUse & currentCell).Value = "") Then
    Exit Do
End If

Dim timeNow As Date
timeNow = Date

Dim willContinue As Boolean
willContinue = True

Dim dateDifference As Integer

dateDifference = dateDiff("d", timeNow, Range(columnToUse & currentCell).Value)

If dateDifference >= 14 And willContinue Then
    Range(columnToUse & currentCell).Interior.ColorIndex = fourteenDays
    willContinue = False
End If

If dateDifference <= 7 And dateDifference > 2 And willContinue Then
    Range(columnToUse & currentCell).Interior.ColorIndex = sevenDays
End If

If dateDifference <= 2 And dateDifference >= 0 And willContinue Then
    Range(columnToUse & currentCell).Interior.ColorIndex = twoDays
End If

If dateDifference < 0 And willContinue Then
    Range(columnToUse & currentCell).Interior.ColorIndex = expired
End If

currentCell = currentCell + 1


End Sub

enter image description here

So, in the developer toolbar, click on Insert and click on button. Drag the shape onto the screen.

Click OK

Right click on the button and name it WorkOutTime

If you don't get the VBa screen, then click on Visual Basic from the ribbon

Delete what is in there and paste my code in.

Save as a macro enabled worksheet. Run it

Please note, my code only works for items in Column A

| improve this answer | |
  • Dave, am I right in saying if I change the "a" to the cell I want it will still work? - Thanks – Jonathan May 2 '14 at 11:30
  • What 'a' do you mean? If you mean the column, then look at the updated code. You can now change the column 1 in place and it will sort the lot out. – Dave May 2 '14 at 12:24
  • Please also note, you can update the code to run when you open the file etc office.microsoft.com/en-gb/excel-help/… – Dave May 2 '14 at 12:46
  • Thank you. I am now having an issue getting it to run. I have changed it to look at column Y. There are a few dates. One being 01/05/2014. When I click the button nothing happens? Does it matter that the tip 2 rows of column Y are merged with X,Y,Z,AA,AB and AC? Ultimately I would like it the change the dates in Y, AA and AC. - Thanks – Jonathan May 2 '14 at 13:16
  • @Jonathan Can you edit your original question and add a screen shot please ... I don't undertsand at the moment. Also, make sure you ping with an @... So, to make sure I know you replied, write @daverook – Dave May 2 '14 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.