Highlight Row and only certain Column cells

  • Thread starter Thread starter ralphdevlin via OfficeKB.com
  • Start date Start date
R

ralphdevlin via OfficeKB.com

I have already tried some macros here for highlighting both the row and colum
when a cell is active. Below is the code I am currently using. Can it be
modified on the colum side to say that the highlighting start at Row 27 and
continue on, as we have froze the upper portion and do not need it
hightlighted as well.

Thanks



'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Target.EntireColumn

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

Private Sub AddRowBorders(pRow As Range)
With pRow
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
.Interior.ColorIndex = 36
End With
End With
End Sub

Private Sub AddColumnBorders(pColumn As Range)
With pColumn
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
.Interior.ColorIndex = 36
End With
End With
End Sub
 
AddColumnBorders Range(Cells(27, Target.Column), Cells(50, Target.Column))
'Target.EntireColumn
 
----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
AddRowBorders Target.EntireRow
With Cells(27,.Column).Resize(rows.count-26,1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Cells(27,.Column).Resize(rows.count-26,1)

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub
 
Back
Top