Bolding cell

TeacherEric

Board Regular
Joined
Mar 21, 2006
Messages
78
Hello all,

I'm stumped... so of course I turn to you guys for the answers. :)

Now that you're good and buttered... :cool:

This is the code I want to run then kept ready (like a TSR).

Every time I move cells the code will run.

The code should start and stop with 1 particular sheet only, not the entire workbook.


Thank you
TeacherEric

Code:
restart:
Dim a As String
Dim b As String

a = ActiveCell.Row
b = ActiveCell.Address
If ActiveCell.Row > 8 Then

' This code should only run if the active ROW is 9 - 63
' the sheet is protected, so no need to validate columns

Range("A" & a & ":" & "C" & a).Select
Selection.Characters.Font.Bold = True
Selection.Characters.Font.Size = 14
Range(b).Select
If ActiveCell.Address <> b Then
Range("A" & a).Characters.Font.Bold = False
Range("A" & a).Characters.Font.Size = 10
GoTo restart

End If
End If
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

Since you have

Range(b).Select

, ActiveCell.Address <> b will never happen.

What are you trying to do?
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
use:
IF (activesheet.name <> "thesheetyouareusing") THEN END
to stop the code when it is executed from a different sheet, or:
put the code in the worksheet_change (or worksheet_selectionchange, depending on what you are doing) of that sheet
 

TeacherEric

Board Regular
Joined
Mar 21, 2006
Messages
78
I guess I have fooled you in thinking I know what I'm doing! Ha-Ha...
:LOL:

Hi

Since you have

Range(b).Select

, ActiveCell.Address <> b will never happen.

What are you trying to do?

I see the problem you pointed out, so maybe you can help with it.

What I'm trying to do is:
I want to keep the focus on the active cell, but change the font size and bold on the cells that are in the same row, but columns A-C.

When I leave that cell, the new active cell is no longer b, so undo the font size and bold, then re-run the code again, changing b 's value to the new active cell address, thereby changing the size and bold on the new row's columns A-C.
repeat this code every time the active cell's value is changed.


-------------

put the code in the worksheet_change (or worksheet_selectionchange, depending on what you are doing) of that sheet

worksheet_selectionchange

I think this is what I want.

Can you give me details on how to do this? I want this code to run only for this sheet, so it should be in the sheet's code, right?

Run the code every time the active cell changes.


-----

Basically doing the same thing that Excel does normally when you change cells.
The row / column headers highlight.
When you're in cell:
G12 -- Column label G is highlighted and Row label 12 is highlighted
B72 -- Column B and Row 72 are highlighted.

But instead of the G & 12 I want the values of A-C (active row) to "highlight" with larger size / bold fonts. When I leave G 12, undo the "highlights" then repeat when I click on the next cell.

I hope this better explains what I'm after. Maybe the code I posted in my first post is totally off base.

Thank again for your continued help.
TeacherEric
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

is this what you after?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("a:c")
   .Font.Bold = False
   .Font.Size = 10
End With
With Range("a" & Target.Row).Resize(,3)
   .Font.Bold = True
   .Font.Size = 14
End With
End Sub
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Code:
Dim lastSelection As Range

'will execute every time the selection changes on this sheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim myRow As Integer

    'if a cell was selected before
    If Not (lastSelection Is Nothing) Then
        'save the row from that selection
        myRow = lastSelection.row
        'remove the bold format from the columns A to C in that row
        Range("A" & myRow & ":C" & myRow).Font.Bold = False
        
        'insert other deformatting here
    End If
    
    'set a new last selection
    Set lastSelection = Target
    
    'save the row from the current selection
    myRow = Target.row
    'set the font format to bold in that row
    Range("A" & myRow & ":C" & myRow).Font.Bold = True

    'insert other formatting here
End Sub

put this code into the module of the worksheet you want to have it applied to.
Btw, jindon's code is nice too, but I hadn't seen before and don't wanna make this for nothing so I thought I should at least post it :biggrin:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Harvey
1) myRow should be Long
2) better Long type global variable than object type
sample:
Code:
Private PrevRow As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If PrevRow > 0 Then
   With Range("a" & PrevRow).Resize(,3)
      .Font.Bold = False
      .Font.Size = 10
   End With
End If
With Range("a" & Target.Row).Resize(,3)
   .Font.Bold = True
   .Font.Size = 14
End With
PrevRow = Target.Row
End Sub
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
as long as he has less than 32768 rows he won't have a problem :LOL:, but, as usual, you are right indeed.
 

smokenack

Active Member
Joined
Dec 12, 2003
Messages
351
I've been playing about Jindon's code to try to get it to work for the active column as well as the active row. Can you help?

thanks

Nick
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
like this?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Cells
   .Font.Bold = False
   .Font.Size = 10
End With
With Target
   .EntireRow.Font.Bold = True
   .EntireRow.Font.Size = 14
   .EntireColumn.Font.Bold = True
   .EntireColumn.Font.Size = 14
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,661
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top