CODEWITHSUNDEEP
excelvbahighlightconditional-formatting
Fahim
Fahim

Reputation: 1

Highlighting Rows and Columns for both single and multiple selected Cells using Conditional Formatting in Excel

I'm trying to highlight the rows and columns for selected cells in Excel It's ok when I'm using conditional formatting to highlight only the row and column of single selected cell

But when I try for selecting multiple cells using mouse or keyboard arrow key only the row and column of first selected cell gets highlighted.

Problem is to highlight rows and columns for both single selected cell and multiple selected cell.

though I had to use VBA to Recalculate the formula. The scenery is this. Using Conditional formatting

the Code is =OR(CELL("col")=COLUMN(),CELL("row")=ROW())

But the want is to highlight the rows and columns of the cells if I Select multiple cells I have Selected C8:C13 Cells So, all the Rows of C8 to C13 need to get highlighted. I tried but couldn't do it. I even tried VBA for highlighting rows and columns with the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Cells.Interior.ColorIndex = xlColorIndexNone

Target.EntireColumn.Interior.ColorIndex = 6 Target.EntireRow.Interior.ColorIndex = 6

End Sub

The result is this. enter image description here

but in VBA the highlighting changes the color of a cell and there is also a problem of copy and paste if this VBA is used then I can't use copy paste a cell

Please if anyone can help?

Upvotes: 0

Views: 941

Answers (1)

Alex
Alex

Reputation: 76

In your conditional formatting rule add this and apply to the range you want:

=isCellInSelectionRowOrColumn(A1)

enter image description here enter image description here

In a module add this:

Function isCellInSelectionRowOrColumn(cell As Range)
    If Not Intersect(Columns(cell.Column), Selection) Is Nothing _
    Or Not Intersect(rows(cell.row), Selection) Is Nothing Then
        isCellInSelectionRowOrColumn = True
    End If
End Function

And in Worksheet code add:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
End Sub

Result is all rows and columns as expansion of your selection get formatted inside the range you apply the rule to:

enter image description here

Upvotes: 0

Related Questions