• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlighting column and rows in current region only

MBS

Member
Hi All,
I want to set the Target as area/region of current region, so that, instead of entire column or entire row, only columns and rows belongs to current region should be highlighted.
Please suggest.

>>> use code - tags <<<
>>> as You've noted earlier <<<

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Area As Range
Set Area = Range("C5").CurrentRegion

Cells.Interior.ColorIndex = xlColorIndexNone

If Target.Row <= Area.Rows.Count And Target.Column <= Area.Columns.Count Then

Target.EntireColumn.Interior.ColorIndex = 4
Target.EntireRow.Interior.ColorIndex = 4


Else
Cells.Interior.Color = xlColorIndexNone
End If


End Sub
 
Last edited by a moderator:
Have a try with these changes:
Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlColorIndexNone
    With Range("A1").CurrentRegion
        If Target.Row <= .Rows.Count And Target.Column <= .Columns.Count Then
            Range(Cells(Target.Row, 1), Cells(Target.Row, .Columns.Count)).Interior.ColorIndex = 4
            Range(Cells(1, Target.Column), Cells(.Rows.Count, Target.Column)).Interior.ColorIndex = 4
        End If
    End With
End Sub
 
or:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Area As Range

Set Area = Range("C5").CurrentRegion
Cells.Interior.ColorIndex = xlColorIndexNone
If Not Intersect(Target, Area) Is Nothing Then
  Intersect(Area, Target.EntireColumn).Interior.ColorIndex = 4
  Intersect(Area, Target.EntireRow).Interior.ColorIndex = 4
End If
End Sub
 
Back
Top