Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA Macro To Find Bold Cells In User Stated Range

  1. #1
    Join Date
    15th November 2008
    Posts
    3

    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. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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

  3. #3
    Join Date
    15th November 2008
    Posts
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Function To Find Bold Cell Values

    Hi,

    I hope Dave won't mind for jumping in.

    Try this

    VB:
    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

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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.

  6. #6
    Join Date
    15th November 2008
    Posts
    3

    Re: Function To Find Bold Cell Values

    Kris, how do I pay you?

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Function To Find Bold Cell Values

    Hi,

    jshaver001,

    Got the payment.

    Thanks

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. VBA Macro To Bold 1st Words In Range
    By lablab in forum Excel General
    Replies: 20
    Last Post: October 27th, 2008, 16:11
  2. Macro To Link Cell To Sheet & Cell Stated By User
    By kjab in forum Excel General
    Replies: 3
    Last Post: June 3rd, 2008, 06:51
  3. Return Maximum Value In Range Stated In Cells
    By Valex in forum Excel General
    Replies: 10
    Last Post: November 18th, 2007, 08:03
  4. Find cells with a border and make them bold
    By dweerdtp in forum Excel General
    Replies: 7
    Last Post: April 13th, 2006, 20:33
  5. Find the second last bold text in a range
    By sekhar in forum Excel General
    Replies: 2
    Last Post: March 31st, 2003, 13:10

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
  •  
porno