VBA: Make selection bold when selected

[bamaisgreat]

I would like it when a cell is selected it will make the text Bold and when you click it again it makes it back to normal ??

 

[Rick Rothstein]

Here is one way to do it. Put the following codes where indicated...


General Module (Insert/Module on VB editor's menu bar, place at top)

Public LastBoldedCell As Range

Worksheet Code Module (double click sheet name in VB editor's Project Window)

Private Sub Worksheet_Activate()
  If LastBoldedCell Is Nothing Then
    Set LastBoldedCell = ActiveCell
    LastBoldedCell.Font.Bold = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  LastBoldedCell.Font.Bold = False
  Target.Font.Bold = True
  Set LastBoldedCell = Target
End Sub

Workbook Code Module (double click ThisWorkbook item in VB editor's Project Window)

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  If Sh.Name = "Sheet2" Then LastBoldedCell.Font.Bold = False
  Set LastBoldedCell = Nothing
End Sub

 

[BAlGaInTl]

This sort of does what you are looking for...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Font.Bold = False Then
    Target.Font.Bold = True
Else
    Target.Font.Bold = False
End If
End Sub

The problem is that it won't work if you click the cell you already have selected.

Could be a starting point though...

 

[BAlGaInTl]

Yeah...

Rick's solution is MUCH better than mine.

 

[Rick Rothstein]

Here is one way to do it. Put the following codes where indicated...


General Module (Insert/Module on VB editor's menu bar, place at top)

Public LastBoldedCell As Range

Worksheet Code Module (double click sheet name in VB editor's Project Window)

Private Sub Worksheet_Activate()
  If LastBoldedCell Is Nothing Then
    Set LastBoldedCell = ActiveCell
    LastBoldedCell.Font.Bold = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  LastBoldedCell.Font.Bold = False
  Target.Font.Bold = True
  Set LastBoldedCell = Target
End Sub

Workbook Code Module (double click ThisWorkbook item in VB editor's Project Window)

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  If Sh.Name = "Sheet2" Then LastBoldedCell.Font.Bold = False
  Set LastBoldedCell = Nothing
End Sub
[/B][/COLOR]

Slight revision... I am not sure what I was thinking, but there is no need to use the Workbook_SheetDeactivate event procedure I showed above as the Worksheet has it own Deactivate event. While the above code works, so will this revision to it (just use what is below and do not include the SheetDeactivate event from the ThisWorkbook code module)...


General Module (Insert/Module on VB editor's menu bar, place at top)

Public LastBoldedCell As Range

Worksheet Code Module (double click sheet name in VB editor's Project Window)

Private Sub Worksheet_Activate()
  If LastBoldedCell Is Nothing Then
    Set LastBoldedCell = ActiveCell
    LastBoldedCell.Font.Bold = True
  End If
End Sub

Private Sub Worksheet_Deactivate()
  If Not LastBoldedCell Is Nothing Then LastBoldedCell.Font.Bold = False
  Set LastBoldedCell = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  LastBoldedCell.Font.Bold = False
  Target.Font.Bold = True
  Set LastBoldedCell = Target
End Sub

 

[taurean]

This should also work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oldcell As Range
If oldcell Is Nothing Then
    Target.Font.Bold = True
    Set oldcell = Target
Else
    oldcell.Font.Bold = False
    Target.Font.Bold = True
    Set oldcell = Target
End If
End Sub

 

[Rick Rothstein]

General Module (Insert/Module on VB editor's menu bar, place at top)

Public LastBoldedCell As Range

Worksheet Code Module (double click sheet name in VB editor's Project Window)

Private Sub Worksheet_Activate()
  If LastBoldedCell Is Nothing Then
    Set LastBoldedCell = ActiveCell
    LastBoldedCell.Font.Bold = True
  End If
End Sub

Private Sub Worksheet_Deactivate()
  If Not LastBoldedCell Is Nothing Then LastBoldedCell.Font.Bold = False
  Set LastBoldedCell = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not LastBoldedCell Is Nothing Then LastBoldedCell.Font.Bold = False
  Target.Font.Bold = True
  Set LastBoldedCell = Target
End Sub

I have to make one change to the above code (I ran across certain circumstances where the above red highlighted test that I added was needed.

 

[Rick Rothstein]

This should also work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oldcell As Range
If oldcell Is Nothing Then
    Target.Font.Bold = True
    Set oldcell = Target
Else
    oldcell.Font.Bold = False
    Target.Font.Bold = True
    Set oldcell = Target
End If
End Sub

One possible minor problem with it... select a cell on the managed sheet, then select another sheet, then activate the "Go To" dialog box (ALT+EG) and type a reference back to the managed sheet to a cell different than the one that was selected when you switched sheets... both the previously selected and newly selected cells will both be highlighted.

 

[bamaisgreat]

Here is one way to do it. Put the following codes where indicated...


General Module (Insert/Module on VB editor's menu bar, place at top)

Public LastBoldedCell As Range

Worksheet Code Module (double click sheet name in VB editor's Project Window)

Private Sub Worksheet_Activate()
  If LastBoldedCell Is Nothing Then
    Set LastBoldedCell = ActiveCell
    LastBoldedCell.Font.Bold = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  LastBoldedCell.Font.Bold = False
  Target.Font.Bold = True
  Set LastBoldedCell = Target
End Sub

Workbook Code Module (double click ThisWorkbook item in VB editor's Project Window)

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  If Sh.Name = "Sheet2" Then LastBoldedCell.Font.Bold = False
  Set LastBoldedCell = Nothing
End Sub

I tried clicking in a cell that had data in it and it debugged " Object variable or With block variable not set"

 

[Rick Rothstein]

I tried clicking in a cell that had data in it and it debugged " Object variable or With block variable not set"

Try using the revised code I posted in Message #7 as I think it addresses that issue (be sure to read Message #5 to make sure you have all the right code installed). For future consideration... you should read all the messages before using the solution from any one of them so that you can see correction that were posted later on.