Formatting cells - December issue!

[Guest]

Hi there,

I have an issue (or possibly have issues), I have set up this code which
will go through my data and see if there is an action to be looked at or that
was meant to be looked at and hasn't and highlight the cell in a colour. It
works how i want it on the most part - but for some reason every date that is
Dec-07 seems to highlight as well regardless of the other data around it.
Would anyone know why this is? (Code is below)

Sub changeColour()
Dim myR1 As Range
Dim wbBook As Workbook
Dim wsMain As Worksheet
Dim myCell1 As Range
Dim aValue As String

Set wbBook = ThisWorkbook
Set wsMain = wbBook.Worksheets("Sheet1")
Set myR1 = wsMain.Range("J2:J168")

For Each myCell1 In myR1

If myCell1.Offset(0, -2).Value <> "" And _
Month(myCell1.Offset(0, -9).Value) < Month(myCell1.Offset(0, -2).Value)
Or _
Month(myCell1.Offset(0, -9).Value) = Month(myCell1.Offset(0, -2).Value) _
Then
myCell1.Select
myCell1.Interior.ColorIndex = 37
Else
myCell1.Interior.ColorIndex = 0
End If

Next myCell1

End Sub

 

[Guest]

Patsy,

It isn't a December issue it will happen when any 2 dates are in the same
month becuase you are using month(mycel.....

Try this:-

Sub changeColour()
Dim myR1 As Range
Dim wbBook As Workbook
Dim wsMain As Worksheet
Dim myCell1 As Range
Dim aValue As String

Set wbBook = ThisWorkbook
Set wsMain = wbBook.Worksheets("Sheet1")
Set myR1 = wsMain.Range("J2:J168")

For Each myCell1 In myR1

If myCell1.Offset(0, -2).Value <> "" And (myCell1.Offset(0, -9).Value) <=
(myCell1.Offset(0, -2).Value) Then
myCell1.Select
myCell1.Interior.ColorIndex = 37
Else
myCell1.Interior.ColorIndex = 0
End If
Next myCell1
End Sub

Note iv'e also deleted the OR bit and use >= instead.

Mike

 

[Dave Peterson]

If it works for all dates except this one, then I'd expect that that value isn't
really a date.

I'd reformat that cell(s) as date and reenter the value.