VBA to format adjacent cells bold

Microsoft Microsoft excel 2007 (pc)
February 6, 2010 at 18:14:23
Specs: Windows XP

I create Excel tables for reports with laboratory analytical results of water samples the require one column with the result and the adjacent cell to the right with a data qualifier, like a "U" for undetected or blank for a detection (there is always some number in the result column that is either at or above the detection limit). Having the detected data stand out by formatting the cells Bold is helpful when presenting a table in a report. I currently do this one cell at a time with Crtl-B.

I'm trying to figure out how to quickly change the format of cells to Bold across a Range. The columns will be:

(data)  (qual)  (data)  (qual)
Col-H	Col-I	Col-J	Col-K  ...and so on...
0.1	U	0.5	U
7.5		0.2	U
6.2		1.5	
0.5	U	0.2	U

...and so on...

If the adjacent cell has a "U" qualifier (undetected), do nothing to the data cell; if the adjacent is blank (detected), make the data cell Bold.

I know I can do Conditional Formatting by selecting the range of cells in Col-A to format to Bold by "=$B1<> "U". However, using CF can create havoc because we sometimes have to add columns/rows to a table over time, or copy the table to other worksheets to present in table format, among other modifications.

So I think I need a VBA macro to Bold all the detected results, where I can adjust the number of rows/columns in the code as needed in the future.

Something like below (it doesn't work though, it blows up, but I think I'm on the right track):


Sub Bold_Detections()

For col = 8 To 120 Step 2 
     'start at col-H then move over two columns as it loops
  For rw = 2 To 63       
     'start at row-3
        If Cells(rw, col) > 0 And Cells(rw, col + 1) <> "U" Then _   
      'if cell is not >0 and there is no "U" in adjacent cell
            Cells(rw, col).Format.Bold = True      
      'this is where the debugger catches a runtime error
End Sub

I think I'm saying to check Col-B to see that all data are not zero, and if the adjacent cells in Col-C are not "U" then make those cells bold.

See More: VBA to format adjacent cells bold

Report •

February 6, 2010 at 18:30:22
I'll simply point out what's causing the error, then you can continue to work on the code.

You appear to be on the right track, so this will be a good learning experience. If you really need help, come on back...we're not going anywhere. ;-)

At the end of this line, you used the underscore Continuation Character:

If Cells(rw, col) > 0 And Cells(rw, col + 1) <> "U" Then _ 

However, following that you inserted a comment. That won't work since you've essentially put a comment right in the middle of the line. VBA doesn't know what to do with that, so it blows up.

The continuation character requires that the rest of that line of code be directly below it. No line feeds, comments or anything other than some alignment spaces.

Remove that comment (or move it) and see if you can get the code to do what you want.

Report •

February 6, 2010 at 19:33:50
Thanks for the really quick response! I didn't know the Forum worked on weekends. :)

The comment was added for this post only, just to show that I was explaining what I was hoping the code was doing. I put it there so it would fit in the Message window.

The code I've been running, and failing, has no comments at all. The VB debugger was highlighting this bit in yellow:

Cells(rw, col).Format.Bold = True

Report •

February 6, 2010 at 19:57:14
The Forum never sleeps!

Cells(rw, col).Font.Bold = True

Had you placed your cursor on the word Format and hit F1, it would have opened the VBA help window for Format.

At that point you probably would have realized that that was not what you wanted to use.

Report •

Related Solutions

February 6, 2010 at 22:18:06
¬°Ay, caramba!

Font!? Format?! I saw the word "Format" but was thinking "Font": They both start with "F's"!

It works!

Thanks, DerbyDad, now I can go to sleep, you do the same!

Report •

February 7, 2010 at 10:14:56
I've been working more on this and I thought to make it user friendly by allowing the user to enter the start/end cols/rows to run the macro. I got it all to work fine, but if I didn't enter any numbers and clicked "OK" or clicked "Cancel" in the message input boxes, I kept getting the "run-time error '13'". So I added the error handler which works great.

The next thing I realized is that if the user enters the number of the column that has cells with the "U's" and/or blanks, then all the "U's" are Bold. The user needs to be told that if the column entered does not have data and only "U's"/blanks, to enter a valid column. This error handler would have to be able to determine that the column choice does in fact have "U's"/blanks.

Another thing that would be nice, that I can't figure out, is to have the user be able to choose the column by letter (i.e. "B") instead of a number so they don't have to count over xNumber of columns.

Now that I think about it, to reduce the number of input boxes, and potential errors, maybe it would be best to allow user to enter the exact cell location to start, like: "H3" then the macro runs from there. There would be less chance of choosing the wrong column, but it should still have an error handler to let them know they are starting in the wrong column. I'm just not quite adept enough to figure it out, yet.

Current Macro:

Sub Bold_Detections()

    On Error GoTo ErrHandler 'if error occurs jump to error handler

    Dim colStart As Integer     
    Dim colEnd As Integer      
    Dim rowStart As Integer     
    Dim rowEnd As Integer      
    Dim Resp As String              
        colStart = InputBox("Enter the Start Column.")      
        colEnd = InputBox("Enter the End Column.")
        rowStart = InputBox("Enter the Start Row.")
        rowEnd = InputBox("Enter the End Row.")
            For col = colStart To colEnd Step 2     
            For rw = rowStart To rowEnd             
            If Cells(rw, col) > 0 And Cells(rw, col + 1) <> "U" Then _
                Cells(rw, col).Font.Bold = True
    Exit Sub    'this exits the macro after execution if no errors are found
 Resp = MsgBox("You did not enter data or clicked ""Cancel"": Macro Canceled.", _
  If Resp = vbCancel Then
    End ' This ends the macro without execution
      End If

End Sub

Report •

February 7, 2010 at 10:51:45
Everything you are asking for can be done, such as checking for empty columns or U's or whatever.

You can also force the entry of a Range by using the Input method which is different than the InputBox function, which you are using.

Dim rngStart As Range
Dim rngEnd As Range
  Set rngStart = Application.InputBox("Enter the Starting Cell.", Type:=8)
  Set rngEnd = Application.InputBox("Enter the Ending Cell.",  Type:=8)

Type:= 8 forces the user to enter a Range reference.

However, with all the possibilities for errors, and the stupid user tricks we all see, I think you need to make this more bullet proof.

Some options that come to mind...

- Have the user select a Range and then run the macro on that selection. Less chance of an typographical error in the InputBox

- Use the code I suggested above, but ask the user to drag through the cells they want to check while the InputBox is visible. That will input the Range into the InputBox. You won't need 2 InputBoxes since the the entire Range can be placed into one InputBox.

Of course, this won't prevent the user from selecting or entering the wrong Range. You could use Column headings and then check to make sure that the first cell in the selected range matches a Column heading. If the first cell was "valid", you could then adjust the range within the macro to ensure it had the correct number of columns for the macro to work. That way, as long as the code ensures that the first column that the user selected was a data column, you could adjust the range so that it matches your worksheet.

One issue I'm not sure how to deal with is the fact that the user could select more or less rows or columns than they really wanted to check. I haven't figured out how to write a macro that reads minds, so if the user doesn't select or enter the correct Range, there's not much anyone can do about that - unless there is something like a "column ending label" that you could check for.

Report •

February 7, 2010 at 13:11:11
Lots to consider. Will play around the selecting the range by dragging through the cells. That's something we do as second nature. And even if the wrong column is selected after running macro, it's easy to undo the Bold formatting and try again.

I'll work on more idiot-proofing the wrong column issue after I figure out the selecting the range bit.

Thanks again.

Report •

February 8, 2010 at 09:51:42
I played around with this while not watching the Super Bowl yesterday. I got the range selection input box to work, but I haven't figured out how to declare "col" and "rw" when using the "rRange" selection. (see the ?? marks at the start of the FOR loop)

Subsequently, I get stuck at the IF statement when selecting the Range and telling the macro to look in the data column to look for the "Cells(rw, col) > 0" value then check "(rw, col + 1)" for a "U".

I'm guessing I need to change the "rw, col" variables, just not sure to what. My limited VBA experience is, well, limiting.

Sub Bold_Detections2()

    Dim Resp As String
    Dim rRange As Range
    On Error GoTo ErrHandler
          Set rRange = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
            For col = ?? To ?? Step 2
            For rw = ?? To ??
            Application.ScreenUpdating = False
    If ?rRange?.Cells(rw, col) > 0 And ?rRange?.Cells(rw, col + 1) <> "U" Then _
                ?rRange?.Cells(rw, col).Font.Bold = True
    Exit Sub
 Resp = MsgBox("You did not enter data or clicked ""Cancel"": Macro Canceled.", _
            If Resp = vbCancel Then
            End If

End Sub

Report •

February 8, 2010 at 11:07:39
To answer your direct question, this code should get you what you want.

          Set rRange = Application.InputBox(Prompt:= _
                "Please select a range with your Mouse to be bolded.", _
                    Title:="SPECIFY RANGE", Type:=8)
     'Determine the Column number of the first Column in the range
            firstCol = rRange.Columns(1).Column
     'I'll leave the rest for you to study
            firstRow = rRange.Rows(1).Row
            lastCol = firstCol + rRange.Columns.Count - 1
            lastRow = firstRow + rRange.Rows.Count - 1

Report •

February 9, 2010 at 07:45:00

I'll chew on this for a bit.

Report •

Ask Question