+ Reply to Thread
Results 1 to 15 of 15

If a name in one range exists in another, then make that name bold....

Hybrid View

  1. #1
    Registered User
    Join Date
    01-11-2016
    Location
    USA
    MS-Off Ver
    2007
    Posts
    22

    Re: If a name in one range exists in another, then make that name bold....

    I have a dynamic list of names in C13:C60. (List of Managers)
    There are several corresponding lists, waiters, bussers etc.
    They all populate another list, N13:N250.
    I want to distinguish the Managers in N13:N150 by making them Bold.Italic
    No matter how I format the cells in C13:C60, after they are transferred to N13:N250, they take on the formatting value (regular) of those cells.

    Here is the full code, It's lengthy so I'll bold where the transfer takes place:
    Sub UpdateSchedule()
    '
    '
    ' Update Schedule Macro
    
    Range("C13:C60").Select
        Range("C60").Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("C60"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("C13:C60")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    ' SortPieman Macro
    '
    Range("E13:E60").Select
        Range("E60").Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("E60"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("E13:E60")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    ' SortWaiter Macro
    '
    Range("G13:G60").Select
        Range("G60").Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("G60"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("G13:G60")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    ' SortBusser Macro
    '
    Range("I13:I60").Select
        Range("I60").Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("I60"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("I13:I60")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    '' SortDriver Macro
    '
    Range("K13:K60").Select
        Range("K60").Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key:=Range("K60"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("K13:K60")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
       Range("C13:C60").Select
        ActiveSheet.Unprotect
        Range("N151:U151").Select
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = True
        End With
        Selection.unmerge
        Range("C13:C60").Select
        Selection.Copy
        Range("N23").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("E13:E60").Select
        Selection.Copy
        Range("N70").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("G13:G60").Select
        Selection.Copy
        Range("N118").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("I13:I60").Select
        Selection.Copy
        Range("N166").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("K13:K60").Select
        Selection.Copy
        Range("N214").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("N23:N275").Select
        ActiveSheet.Range("$N$23:$N$275").RemoveDuplicates Columns:=1, Header:=xlNo
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort.SortFields.Add Key _
            :=Range("N261"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortNormal
        With ActiveWorkbook.Worksheets("Scheduling Assistant 1.0").Sort
            .SetRange Range("N23:N261")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("N22:N150").Select
         
        
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99786370433668E-02
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        ActiveWindow.SmallScroll Down:=129
        Range("N151:U151").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99786370433668E-02
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Dim x, y, i As Long, e, dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        x = [if((X7:BS55<>"")*(countif(c13:k95,X7:BS55)=0),X7:BS55,char(2))]
        For i = 1 To UBound(x)
            y = Filter(Application.Index(x, i, 0), Chr(2), 0)
            If UBound(y) > -1 Then
                For Each e In y
                    dic(e) = Empty
                Next
            End If
        Next
        Range("C8").Select
        If dic.Count Then MsgBox "Please Unschedule" & vbLf & Join(dic.keys, vbLf), 16
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        
    End Sub
    Last edited by rcicconetti; 01-13-2016 at 07:12 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Make the Zero line bold
    By mra1984 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 09-08-2017, 06:27 PM
  2. Make the number bold
    By pietka in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-16-2014, 04:51 AM
  3. [SOLVED] Make string bold
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2013, 02:33 PM
  4. [SOLVED] how do i make certain words bold?
    By pablowilks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2013, 02:48 PM
  5. how can I make this BOLD?
    By Mamud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2012, 06:15 AM
  6. Conditional Format-make bold the lowest value in a range
    By uplink600 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2007, 07:47 AM
  7. Make string bold?
    By Adam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2005, 11:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1