Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA Macro To Find Bold Cells In User Stated Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Macro To Find Bold Cells In User Stated Range

    I need a function that does the following:

    1. Examines all of the cells in a user selected range to determine if any of the values in the range are bold.

    2. Where a bold cell is found (limit of one bold cell per row), its value is copied to a range (single column, multiple rows) specified by the user (by specifying the starting cell as an argument to the function)

    3. If multiple bold cells are found, the string "Multiple Values" copied to the appropriate cell in the range which would otherwise have contained the copied value

    The function with arguments might look like this:

    =BoldFinder (A1:H60,B11)

    I would be happy to pay someone $25 by whatever convenient means for some help on this very quickly.

  • #2
    Re: Function To Find Bold Cell Values

    A Function cannot make changes or Copy to other cells. It will have to be a macro procedure with ranges collected from the user via an InputBox.

    If you are OK with that, please attach a small BUT relevant before and after example file

    Comment


    • #3
      Re: Function To Find Bold Cell Values

      OK Here is a file that illustrates my need.

      If you can do this, I'd be thrilled.
      Attached Files

      Comment


      • #4
        Re: Function To Find Bold Cell Values

        Hi,

        I hope Dave won't mind for jumping in.

        Try this

        Sub test()
        Dim SourceRng As Range, DestCell As Range
        Dim Count As Long, i As Long, j As Long, n As Long, w(), temp
        On Error Resume Next
        Set SourceRng = Application.InputBox("Select the Range", "Source Range", Type:=8)
        Set DestCell = Application.InputBox("Select the Destination Cell", "Output Cell", Type:=8)
        On Error GoTo 0
        If Not SourceRng Is Nothing Then
        If Not DestCell Is Nothing Then
        ReDim w(1 To UBound(SourceRng.Value, 1), 1 To 1)
        With SourceRng
        For i = 1 To UBound(SourceRng.Value, 1)
        For j = 1 To .Columns.Count
        If ISBOLD(.Cells(i, j)) Then
        Count = Count + 1
        temp = .Cells(i, j).Value
        End If
        Next
        n = n + 1
        If Count = 1 Then
        w(n, 1) = temp
        ElseIf Count > 1 Then
        w(n, 1) = "Multiple Values"
        Else
        w(n, 1) = "No Values"
        End If
        Count = 0: temp = Empty
        Next
        End With
        With DestCell
        .Resize(n, 1).Value = w
        .Resize(n, 1).Font.Bold = True
        End With
        Else
        MsgBox "Not a valid cell", vbCritical
        Exit Sub
        End If
        Else
        MsgBox "Not a valid range", vbCritical
        Exit Sub
        End If
        End Sub
        Function ISBOLD(r As Range) As Boolean
        Dim i As Long
        ISBOLD = False
        With r
        For i = 1 To Len(r)
        If .Characters(i, 1).Font.Bold = True Then
        ISBOLD = True
        Exit For
        End If
        Next
        End With
        End Function


        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Function To Find Bold Cell Values

          Hey, kris. Nah, go for it, I'm going to be in and out all day Today.

          jshaver001, pay Kris,not me.

          Comment


          • #6
            Re: Function To Find Bold Cell Values

            Kris, how do I pay you?

            Comment


            • #7
              Re: Function To Find Bold Cell Values

              Hi,

              jshaver001,

              Got the payment.

              Thanks
              Kris

              ExcelFox

              Comment

              Trending

              Collapse

              • maichal
                auto generate id in user form save sale purchase data on sale & purchase sheet
                maichal
                i am add add sale transaction & add purchase transaction command button & the id was Auto generate in text box 1 if i am choose sale in combo box 1 than sale-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add sale transaction command button the data was add on the sale sheet,if i am choose Purchase in combo box 1 than Purc-001 id was Auto generate in text box 1 add the name in combo box 2 & click on add Purchase transaction command button the data was add on the Purchase sheet, please solve this sir, after add the information in sale & purchase excel sheet, i am choose sale in combo box 1 the id was auto generate in id text box 1, than i am choose the name in combo box 2, if i am choose sale in combo box 1 than combo box 2 show only sale Customer...
                2 days ago
              • DiogoCuba
                US$20 to Automate Crew Roster
                DiogoCuba
                Hi, I have a Crew Roster that controls the crew changes of a certain vessel and I would like to automate it to avoid wasting time with manual copy and paste.

                Anybody willing to help me?

                Some of the actions I need:
                • Create a Pax list and a Flight Manifest based on the personnel assigned to embark and disembark;
                • Generate a Daily POB based on the names that are onboard (Including the dates of embark and a counter to count how many days the person is onboard);
                • Compare the Daily POB to a criteria to see if the safe manning is compliant or not;
                • Create a PAX LIST for the next 7 days automatically so that I can see who is crew change and who is not - this should be triggered based on the dates;
                • Auto generate Flight Manifest when I click a button and input the
                ...
                June 27th, 2017, 09:47
              Working...
              X