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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi

Since you have

Range(b).Select

, ActiveCell.Address <> b will never happen.

What are you trying to do?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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
 
Upvote 0
as long as he has less than 32768 rows he won't have a problem :LOL:, but, as usual, you are right indeed.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top