If Function with Bold Font

Microsoft Excel 2007
February 11, 2010 at 19:43:07
Specs: Windows 7

I'm trying to create a March Madness bracket
in Excel where in order to select a team to win
a given game, all you have to do is bold all the
contents in that cell. For example:

Cell A1 is North Carolina
Cell A3 is Boston College
Cell B2 is the winner of NC vs. BC

If the user bolds Cell A1, then the contents of
A1 show up in B2. I would then need North
Carolina to be unbolded in B2.

See More: If Function with Bold Font

February 11, 2010 at 22:12:36
You can't use any built in Excel functions to Bold or check the formatting of a cell. You have to use VBA.

I created a Bracket Sheet that might work for you which is based on the Selection_Change event. When the user clicks a cell with a team name in it, that team is bolded and the name is placed in the next bracket location.

I'll include the code below, but I think you really need to see the spreadsheet to see how it works. The spreadsheet has named ranges in it so that the code knows which cell was selected and can determine where to place the selected name in the column.

If you are interested in seeing the spreadsheet, PM an email address and I'll send it along.

Here's the code, just for fun. If you want to try it on your own, enter your teams in A1&A3, A5&A7, A9&A11, etc. Then give each of the following ranges a unique name.

A1:A3, A5:A7, A9:A11, A13:A15, B2:B6, B10:B14, C4:C12

This will get you through 8 teams (4 games) If you can figure out how to make this work for the 8 teams, then you can expand it for the entire bracket sheet.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nm
'Make sure only 1 cell is selected
 If Target.Cells.Count = 1 Then
'Loop through Named Ranges to find out
'which Named Range the cell is in
  For Each nm In ActiveWorkbook.Names
   If Not Intersect(Target, Range(nm)) Is Nothing Then
'Unbold the Range (Allows for changes)
    Range(nm).Font.Bold = False
'Bold the selected cell
    Target.Font.Bold = True
     With Range(nm)
'Determine the cell location in the next column
'that will be centered in the current range
     If Target.Address = .Cells(1).Address Then
         Target.Offset(Int(.Cells.Count / 2), 1) = Target.Value
         Target.Offset(Int(.Cells.Count / 2), 1).Font.Bold = False
     Else: Target.Offset(Int(.Cells.Count / 2) * -1, 1) = Target
           Target.Offset(Int(.Cells.Count / 2) * -1, 1).Font.Bold = False
     End If
    End With
   End If
 End If
End Sub

Report •
Related Solutions

Ask Question