highlight active of row of set range

[charly1]

hi

Is there an easy way with vba coding, that when the active cell is in range D11:I20, the active row - limited to that range [i.e active row, col D-I], will be: 1. highlighted with a cell back colour, 2. the writing be set to bold and 3. for that entire row in that range to be surrounded by a thick border.

Important though is that once active cell changes [either by moving to a different row in range, or by leaving that range entirely], the properties all revert back to how they were originally.

Any help would be greatly appreciated.

 

[Jeffrey Mahoney]

Read this and see if fits your needs. Ask me questions

Solution: Update Conditional Formatting Without Calculating Sheet

 

[dreid1011]

Perhaps this:

Change "Sheet1" to the name of your worksheet and current fill color is yellow. That can be changed by adjusting the ColorIndex value based on this table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim i As Integer
    i = 4 ' Loop 1 column counter
    Set ws = Worksheets("Sheet1")
    If Not Intersect(Target, Range("D11:I20")) Is Nothing Then
        With ws.Range("D11:I20")
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
        For i = 4 To 9
            ws.Cells(Target.Row, i).Interior.ColorIndex = 6
            ws.Cells(Target.Row, i).Interior.Pattern = xlSolid
            ws.Cells(Target.Row, i).Font.Bold = True
            With ws.Cells(Target.Row, i).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With ws.Cells(Target.Row, i).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            Select Case i
                Case 4
                    With ws.Cells(Target.Row, i).Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                Case 9
                    With ws.Cells(Target.Row, i).Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
            End Select
        Next i
    Else
        With ws.Range("D11:I20")
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
    End If
End Sub

 

[charly1]

Perhaps this:

Change "Sheet1" to the name of your worksheet and current fill color is yellow. That can be changed by adjusting the ColorIndex value based on this table:
View attachment 97554

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim i As Integer
    i = 4 ' Loop 1 column counter
    Set ws = Worksheets("Sheet1")
    If Not Intersect(Target, Range("D11:I20")) Is Nothing Then
        With ws.Range("D11:I20")
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
        For i = 4 To 9
            ws.Cells(Target.Row, i).Interior.ColorIndex = 6
            ws.Cells(Target.Row, i).Interior.Pattern = xlSolid
            ws.Cells(Target.Row, i).Font.Bold = True
            With ws.Cells(Target.Row, i).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With ws.Cells(Target.Row, i).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            Select Case i
                Case 4
                    With ws.Cells(Target.Row, i).Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
                Case 9
                    With ws.Cells(Target.Row, i).Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .ColorIndex = 0
                        .TintAndShade = 0
                        .Weight = xlMedium
                    End With
            End Select
        Next i
    Else
        With ws.Range("D11:I20")
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
            .Font.Bold = False
        End With
    End If
End Sub

Thank you so much for your time, and help.
Also big thanks to @Jeffrey Mahoney for his help.

It worked, although unfortunately it works slowly, [it takes around a full second each time I select a cell for the row to be highlighted].
[there is also another issue that the original formatting wasn't blank as can be seen in the screenshot pic above - second rule]

What I found may be another solution. In conditional formatting the following formula

=CELL("row")=ROW()

when applied to my specific range, works much quicker.

My one issue is that with my conditional formatting rule [the first one in the picture above], the active row of my range is highlighted whenever any cell anywhere along the entire row of the entire spreadsheet is selected.

Is there a way to make my conditional formatting rule only apply, when the cell selected is within the applied range?

(I doubt I've suggested anything you guys don't already know, and if you haven't gone down my route in the first place, you probably have good reasons - but I'm just "a new learner", and I suppose if I don't ask I'll always remain just "a new learner"!].

Thanks again

 

[charly1]

Thank you so much for your time, and help.
Also big thanks to @Jeffrey Mahoney for his help.

It worked, although unfortunately it works slowly, [it takes around a full second each time I select a cell for the row to be highlighted].
[there is also another issue that the original formatting wasn't blank as can be seen in the screenshot pic above - second rule]

What I found may be another solution. In conditional formatting the following formula

=CELL("row")=ROW()

View attachment 97579

when applied to my specific range, works much quicker.

My one issue is that with my conditional formatting rule [the first one in the picture above], the active row of my range is highlighted whenever any cell anywhere along the entire row of the entire spreadsheet is selected.

Is there a way to make my conditional formatting rule only apply, when the cell selected is within the applied range?

(I doubt I've suggested anything you guys don't already know, and if you haven't gone down my route in the first place, you probably have good reasons - but I'm just "a new learner", and I suppose if I don't ask I'll always remain just "a new learner"!].

Thanks again

Hi again, I've since been fiddling around and It seems I may have a solution - tell me where I'm wrong!

I have exchanges the conditional formatting rule from:

=CELL("row")=ROW()

To:

=IF(NOT($D$1),"",CELL("row")=ROW())

as in picture below.

In my Vba sheet selection change, I have inserted the following code.

If Target.Column > 3 And Target.Column < 10 And Target.row > 10 And Target.row < 21 Then
Range("D1").Value = "TRUE"
Else
Range("D1").Value = "FALSE"
End If

And everything works beautify and quickly.

As I said above tell me if I'm doing something that may be unadvisable.

 

[Peter_SSs]

Give this method a try. No need for D1 and it will preserve Excel's 'Undo' functionality.

1. Select D11:I20 and apply this CF (remove other CF in a copy of your worksheet first)
   

   =AND(CELL("row")=ROW(),CELL("row")>=11,CELL("row")<=20,CELL("col")>=4,CELL("col")<=9)

   
   
2. Right click the sheet name tab, choose 'View Code' & paste the code below (assuming you don't already have Worksheet_SelectionChange code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

 

[charly1]

Give this method a try. No need for D1 and it will preserve Excel's 'Undo' functionality.

1. Select D11:I20 and apply this CF (remove other CF in a copy of your worksheet first)
   

   =AND(CELL("row")=ROW(),CELL("row")>=11,CELL("row")<=20,CELL("col")>=4,CELL("col")<=9)

   
   
2. Right click the sheet name tab, choose 'View Code' & paste the code below (assuming you don't already have Worksheet_SelectionChange code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

Fantastic!!

Quick note. your formula works perfectly without actually adding the vba coding

 

[Peter_SSs]

your formula works perfectly without actually adding the vba coding

Do you already have a Worksheet_SelectionChange code or other 'event' code in your worksheet? Otherwise there is nothing to trigger the recalculation required to update the conditional formatting.
It certainly does not work and has never worked for me since at least this without the code.