How can I use VBA to highlight the cell 10 rows under the one the user clicks?

[sethspearman]

Hello,

I want to highlight (apply a bold format) around the cell that is 10 cells under the one my user just clicked. I know the vba for applying the formatting. Here is what I don't know how to do.

I only want this to happen if the user clicks on a certain named range. Pseudo code would be

If userclickedCell is in myNamedRange then
    'highlight 10 rows down.
End If

Sub HighlightCell(rowsDown as integer)
  'format the borders of that cell.
End Sub

The other trick is that I want to I want to undo it when the user clicks on another cell. So only one cell is highlighted at a time. When the user clicks anywhere outside of the named range then the bold formatting is removed.

Seth

 

[doofusboy]

Maybe something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(ThisWorkbook.Names("Your_Range").RefersToRange, Target) Is Nothing Then
        Target.Offset(10, 0).Font.Bold = True
    End If
End Sub

 

[Norie]

Try this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Target.Count > 1 Then Exit Sub
 
    If Not Application.Intersect(Range("Your_Range"), Target) Is Nothing Then
        Range("Your_Range").Offset(10).Borders.LineStyle = xlNone
        Target.Offset(10, 0).BorderAround xlContinuous, xlThick, xlColorIndexAutomatic
    Else
        Range("Your_Range").Offset(10).Borders.LineStyle = xlNone
    End If
 
End Sub