Addition to existing code

[ipbr21054]

Morning all,
Hope you had a good Christmas.

Below is a current working code which not only does it high lite the row but also the active cell.

I now have a sheet which i would like to use it on BUT i need to also high lite the column as well as the row / active cell.
The columns in question will be A:K
The high lighted colors for row / column will be the same & take on the current color in use being Interior.ColorIndex = 8

Please could you advise the additional code i need to add & please show where i need to place it.

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"


'   *** Specify start row ***
    If (Target.Row > 4 And Target.Row < 29) Then
          myStartRow = 5
    Else: myStartRow = 29
    End If
'   Use first column to find the last row
    If (Target.Row > 4 And Target.Row < 29) Then
          myLastRow = 28
    Else: myLastRow = 30
    End If
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    Range("A5:K30").Interior.ColorIndex = 2
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   This color will Highlight the row
    If (Target.Row > 4 And Target.Row < 29) Then
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Else
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 3
    End If
'   This color will Highlight the cell in the row
    If (Target.Row > 4 And Target.Row < 29) Then
    Target.Interior.Color = vbGreen
    Else
    Target.Interior.Color = vbRed
    End If
    Application.ScreenUpdating = True


End Sub

 

[MickG]

Add line shown in Red:-

' This color will Highlight the row
    If (Target.Row > 4 And Target.Row < 29) Then
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
   [COLOR="#FF0000"] Range(Cells(4, Target.Column), Cells(29, Target.Column)).Interior.ColorIndex = 8
[/COLOR]    Else
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 3
    End If

 

[ipbr21054]

Thanks very much,works fine.

 

[MickG]

You're welcome

 

[ipbr21054]

Happy New Year.

Code in use is supplied below.

I would like an edit please for only high lite cells which have values in.

My range on the sheet is A5:G28 and to explain for you the last row with text at present is G10
So anything between A5:G10 with be high lighted vertical & horizontal ONLY

Anything after G10 will be the sheets default color which is white.
Row 9,10,11 etc will only change color when values are applied.

Currently what happens is any cell withing the range A5:G28 on the sheet whether it has a value or not is high lighted.
I see it as nothing to high light if no value so dont alter the color.

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "G"


'   *** Specify start row ***
    If (Target.Row > 4 And Target.Row < 29) Then
          myStartRow = 5
    Else: myStartRow = 29
    End If
'   Use first column to find the last row
    If (Target.Row > 4 And Target.Row < 29) Then
          myLastRow = 28
    Else: myLastRow = 30
    End If
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    Range("A5:G30").Interior.ColorIndex = 2
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   This color will Highlight the row
    If (Target.Row > 4 And Target.Row < 29) Then
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
'   This color will Highlight the column
    Range(Cells(5, Target.Column), Cells(28, Target.Column)).Interior.ColorIndex = 8
    Else
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 3
    End If
'   This color will Highlight the cell in the row
    If (Target.Row > 4 And Target.Row < 29) Then
    Target.Interior.Color = vbGreen
    Else
    Target.Interior.Color = vbRed
    End If
    Application.ScreenUpdating = True


End Sub