VBA to highlight and bold text

[dethnode]

I have a workbook with 3 sheets, would like to potentially add more. Sheet 1 is Data! that lists data that I need to update, Sheet 2 and Sheet 3 are pull some information from Data!.

In Data!B2 I type in a date, then that is broken down into the typed out month, day and year in 3 separate cells.

=DATA!J1&DATA!J2&DATA!J3&" regarding our deposit and loan balances. Please confirm the accuracy of "

Data!J1:Data!J3 refers to the date from Data!B1 broken down into month, day, and year.

I need my all sheets other than !data to auto update their respective cell F6 to return the above text string with the date bold and highlighted in yellow whenever cell Data!B1 is changed.

So, If I type December 31, 2024 into Data!B2

I need sheet2!F6 to return

December 31, 2024 regarding our deposit and loan balances. Please confirm the accuracy of

With the Date highlighted

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Data" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in B2 and press the ENTER KEY.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long, y As Long
    For Each ws In Sheets
        If ws.Name <> "Data" Then
            With ws
                .Range("F6") = Format(Target, "mmmm dd, yyyy") & " regarding our deposit and loan balances. Please confirm the accuracy of"
                .Range("F6").Font.Color = vbBlack
                .Range("F6").Font.Bold = False
                y = InStr(1, .Range("F6"), ",") + 5
                For x = 1 To y
                    .Range("F6").Characters(1, x).Font.Color = vbYellow
                    .Range("F6").Characters(1, x).Font.Bold = True
                Next x
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[dethnode]

Thank you, that worked great. Now is it possible to add a button onto the data page that when clicked would print all pages other than data to a pdf using microsoft print to pdf, but would open the destination window folder to select the destination

 

[dethnode]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Data" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in B2 and press the ENTER KEY.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, x As Long, y As Long
    For Each ws In Sheets
        If ws.Name <> "Data" Then
            With ws
                .Range("F6") = Format(Target, "mmmm dd, yyyy") & " regarding our deposit and loan balances. Please confirm the accuracy of"
                .Range("F6").Font.Color = vbBlack
                .Range("F6").Font.Bold = False
                y = InStr(1, .Range("F6"), ",") + 5
                For x = 1 To y
                    .Range("F6").Characters(1, x).Font.Color = vbYellow
                    .Range("F6").Characters(1, x).Font.Bold = True
                Next x
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Thank you, that worked great. Now is it possible to add a button onto the data page that when clicked would print all pages other than data to a pdf using microsoft print to pdf, but would open the destination window folder to select the destination

 

[mumps]

Create a button on the Data sheet. Place the macro below in a regular module and assign it to the button. Change the file name (in red) to suit your needs.

Sub SavePDFs()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wsArr() As Variant, x As Long, sPath As String
    For Each ws In Sheets
        If ws.Name <> "Data" Then
            x = x + 1
            ReDim Preserve wsArr(1 To x)
            wsArr(x) = ws.Name
        End If
    Next ws
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            Sheets(wsArr).Select
            ChDir sPath
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Sheets" _
                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
        End If
    End With
    Application.ScreenUpdating = True
End Sub