Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells - December issue!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells - December issue!
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 "Pasty" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting cells - December issue!
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. Pasty wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting issue | Excel Worksheet Functions | |||
Return Month in word form. 12=December | New Users to Excel | |||
Formula copy for December | Excel Discussion (Misc queries) | |||
Formatting issue ??? | Excel Discussion (Misc queries) | |||
formatting issue | Excel Programming |