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

Hello rcicconetti,

Have you tried Conditional formatting?

Regards.

I would do this using MATCH and Conditional Formatting.

@ FDibbins,

I "hate" you, because you are correct. I assumed linear data.

Thank you for your valued input!

Best Regards.

Hey boetman welcome back. No, you could be correct, depends what the OP is dealing with

It's not a macro, it's a built-in function.

Can you shows us some sample data?

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

Hi rcicconetti,

If you have managed to solve your issue, then please select Thread Tools from the menu link above and mark this thread as SOLVED.

Thanks.

Hello rcicconetti,

...It's lengthy so I'll bold where the transfer takes place:

I cannot see where the transfer takes places with this Code you have pasted in Bold.

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

Please could you attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Also please try to compile the Sample Workbook with all the Macros in place as well.

I suspect it may have to do with this line of Code, further down your Module;

... Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Regards.

Hi, rcicconetti,

maybe try the first part (as I dont recommend to merge cells the bordering and merging is excluded). And rows 13 to 60 make up 48 rows for me while you only add 47 for the next area to copy to...

Sub UpdateSchedule()
'
'
' Update Schedule Macro

Dim lngCtr As Long
Dim lngLine As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets("Scheduling Assistant 1.0")
For lngCtr = 3 To 11 Step 2
  With ws.Sort
    With .SortFields
      .Clear
      .Add Key:=ws.Cells(60, lngCtr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    .SetRange ws.Range(ws.Cells(13, lngCtr), ws.Cells(60, lngCtr))
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
Next lngCtr
    
ws.Unprotect
With Range("N151:U151")
  .HorizontalAlignment = xlGeneral
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  .ReadingOrder = xlContext
  .MergeCells = True
  .UnMerge
End With
lngLine = 23
For lngCtr = 3 To 11
  ws.Cells(13, lngCtr).Resize(48, 1).Copy
  ws.Range("N" & lngLine).Resize(48, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  ws.Range("N" & lngLine).Resize(48, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
  lngLine = lngLine + 48
Next lngLine
    
ws.Range("$N$23:$N$" & lngLine - 48).RemoveDuplicates Columns:=1, Header:=xlNo
    
With ws.Sort
  With .SortFields
    .Clear
    .Add Key:=ws.Range("N23"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  End With
  .SetRange ws.Range("N23:N" & ws.Cells(ws.Rows.Count, "N").End(xlUp).Row)
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

'no further

Code is untested so errors may be raised.

Ciao,
Holger