Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Tacoma, WA, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Red face Macro works in EXCEL 2007 but not in 2010

    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.

    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
    '
        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
    Last edited by RetiredGeek; 2015-04-07 at 20:20. Reason: Added Code Tags

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

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

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    dkv98446 (2015-04-08)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  5. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Tacoma, WA, USA
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •