Macro works in EXCEL 2007 but not in 2010

Topic

New Reply

The following macro is the result of combining 4 small macros into one. All of the pieces work individually & when combined in EXCEL 2007. When combined and run in my user’s office everything except the sort by cell color works correctly. I’ve enlarged and changed the font to green bold in the section I’m having trouble with.
What I need the routine to do is choose the members of our organization who will have birthdays in the next month, change the birth-date to bold green, the cell background to light green, and group them at the top of the list in day order.
40122-LAST-NAME

Code:

Public Sub SortBD()
'
' SortBD Macro
'

' Group selected members with birthdays in the next MONTH
'
'
' Look a "BIRTH DATE" in Column "I" and
' Select records with Birthday in the next month
' Change font to green and make bold and set cell back ground to light green

    Range("I1:I400").Select
 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MONTH($I1)=MOD(MONTH(TODAY()),12)+1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
        .Color = -16751104
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
'
'    SORT selected rows to the top based on CELL Color then
'         SORT selected rows into day (DD) order
'
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Clear
'
  [SIZE=4][B][COLOR=#008000]  ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add(Range("I2:I240"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(215, _
           228, 188)[/COLOR][/B][/SIZE]
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("Y2:Y240" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("A2:A240" _
       ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("B2:B240" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    With ActiveWorkbook.Worksheets("Current").Sort
       .SetRange Range("A1:Y240")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With
    
    
 ' End SORT
 '
 '
 ' Hide Columns
 '
    Range("C:F,H:H,J:K").Select
    Range("J1").Activate
    Selection.EntireColumn.Hidden = True
    
End Sub

Reply | Quote

Replies

Hi

I can confirm that this is an issue.
I am looking in to it.

zeddy

Reply | Quote

Hi

Boy that was tough to figure out what was happening!

Your Excel2007 code doesn’t work with Excel2010 (or Excel2013) because there is a ‘slight’ discrepancy with the RGB values for the theme colour values!

For Excel2007 use:
.SortOnValue.Color = RGB(215, 228, 188)

For Excel2010/Excel2013 use:
.SortOnValue.Color = RGB(216, 228, 188)

..don’t ask me why!!!!

But, perhaps the best solution is to directly specify the color code rather than use .ThemeColor = xlThemeColorAccent3

here is my modified code that will keep the same colours, but will work in Excel2007, 2010 and 2013:

Code:

Public Sub SortBD()
'
' SortBD Macro
'

' Group selected members with birthdays in the next MONTH
'
'
' Look a "BIRTH DATE" in Column "I" and
' Select records with Birthday in the next month
' Change font to green and make bold and set cell back ground to light green

    Range("I1:I400").Select
 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MONTH($I1)=MOD(MONTH(TODAY()),12)+1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
        .Color = -16751104
        .TintAndShade = 0
    End With
''''    With Selection.FormatConditions(1).Interior
''''        .PatternColorIndex = xlAutomatic
''''        .ThemeColor = xlThemeColorAccent3
''''        .TintAndShade = 0.599963377788629
''''    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12379351
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False
    
'
'    SORT selected rows to the top based on CELL Color then
'         SORT selected rows into day (DD) order
'
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Clear
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add(Range("I2:I240"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(215, _
           228, 188)
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("Y2:Y240" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("A2:A240" _
       ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    ActiveWorkbook.Worksheets("Current").Sort.SortFields.Add Key:=Range("B2:B240" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
    With ActiveWorkbook.Worksheets("Current").Sort
       .SetRange Range("A1:Y240")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
    End With
    
    
 ' End SORT
 '
 '
 ' Hide Columns
 '
    Range("C:F,H:H,J:K").Select
    Range("J1").Activate
    Selection.EntireColumn.Hidden = True
    
End Sub

zeddy

Reply | Quote

Thank you so much. ;):)
I do not have enough knowledge to even come close to ever solving this. You get ***** (5) stars for your help,

Reply | Quote