Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Highlight row and column of active cell

Color highlights intersection of columns and rows using a formula for conditional formatting, or VBA macro

This web site is a collection formula and VBA macros that can be found on web pages the Internet network. All credit goes to the authors VBA macros. My goal is to put in one place the various variants of VBA code that you can use color highlight a cell, row, and column after clicking on the active cell.

How to highlight the color of the active cell (cell shading)

One way of highlighting or visual marking color the active cell using VBA macro is shown below.

Highlight or select color the active cell

VBA macro used for cell shading color in the image above. VBA macros in the following. This VBA code copy in the ThisWorkbook module.


Option Explicit
Public address1 As Integer
Public address2 As Integer
Private Sub Workbook_Open()
ActiveCell.Interior.ColorIndex = 36
address1 = ActiveCell.Row
address2 = ActiveCell.Column
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
ActiveCell.Interior.ColorIndex = 36
If address1 <> 0 And address2 <> 0 Then
ActiveSheet.Cells(address1, address2).Interior.ColorIndex = xlNone
End If
address1 = Target.Row
address2 = Target.Column
End Sub

How to highlight the active row or column in Excel after clicking the cell

If you want color highlight the active row and column then use this VBA macro below image.

How to coloring active row and column after clicking the cell

VBA macro for coloring row and column after clicking the cell is below. This VBA macro to copy in the Sheet Module on which you want to highlight the effect of color the active row/column.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
'Cells.Interior.ColorIndex = xlNone ' Clear the color of all the cells
ActiveSheet.Cells.Interior.ColorIndex = 0
With ActiveCell
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 36
.EntireColumn.Interior.ColorIndex = 36
End With
Application.ScreenUpdating = True
End Sub


Color highlights the intersections of row and column of active cells using the "Conditional Formatting"
Another way to use Conditional Formatting and formula. The CF set the following formula

=OR(CELL("col")=CELL("col";A1);CELL("row")=CELL("row";A1))

or another formula

=OR(CELL("col")=COLUMN();CELL("row")=ROW())

Highlight color the row and column of the active cell using conditional formatting and formula
In order to coloring/highlighting active row and column in this case functioned, in the Sheet Module respective worksheet is necessary to set the following VBA code

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

Highlight a part of row and column the active cell

If you want to highlight or coloring only part of the row or column after clicking on the active cell (left and above of the active cell) then use the following VBA macro below.

Highlight a portion of row and column the active cell

This VBA macro to copy the Module Sheet respective worksheet.

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


On the same worksheet set Conditional Formatting and formula below. The formula should be in a single line of code.

=OR(AND(CELL("col")=CELL("col";A1);CELL("row")>CELL("row";A1));AND(CELL("row")=CELL("row";A1);CELL("col")>CELL("col";A1)))

Mark or highlighting the row and column the active cell
If you want to highlight only frame of the row and column after selecting a cell then use the following formula using Conditional Formatting in Excel.

The formula for highlighting row

=AND(CELL("row")=CELL("row";A1);CELL("col")>CELL("col";A1))

The formula for highlighting column

=AND(CELL("col")=CELL("col";A1);CELL("row")>CELL("row";A1))

To mark or highlight the row and column of the active cell

Of course, here you need to use VBA macro for automatic calculation of the worksheet when the current changes in the selected cell. This VBA macro below in copy sheet Module respective worksheet.

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

Selecting entire row the active cell in Excel

If you need only review some information and you want to highlight a row of the active cell then use VBA macro below.
Note! If you use the VBA macro, you have the option of adding/inserting columns or selecting some cells specifically. Excel always selects or indicates a whole row and each command refers to the active row.
Highlight color row active or selected cell in Excel

This VBA macro below you copy into Module Sheet respective worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Rows(Target.Row).Select ' highlite entire row
Target.Activate ' selected cell
End Sub

Double click the mouse highlights the intersection of row and column the active cell

If you want to have control of the row and column of the active cell that you want to visual highlight the row and column then use this VBA macro below. This VBA macro below copy into sheet Module respective worksheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strRange As String
strRange = Target.Cells.Address & "," & _
Target.Cells.EntireColumn.Address & "," & _
Target.Cells.EntireRow.Address
Range(strRange).Select
End Sub

Double click the mouse highlights the row and column of the active cell

Color highlights multiple rows and columns after selecting multiple cells

If you want to when working in Excel, you can automatically highlight all rows and columns, after clicking on more non-adjacent cells. (see figure below).

Color highlights multiple rows and columns after selecting multiple cells

The formula below set into the Conditional Formatting respective worksheet

=OR(SUMPRODUCT((ROW(A1)>=MinRows)*(ROW(A1)<=MaxRows))>0;SUMPRODUCT((COLUMN(A1)>=MinCOls)*(COLUMN(A1)<=MaxCols))>0)

In order to this coloring active rows and columns to work, you need to use VBA.
VBA code or macro below, copy into Sheet Module of the respective worksheet

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim anArea As Range, MinRow As String, MaxRow As String, _
MinCol As String, MaxCol As String
For Each anArea In Target.Areas
With anArea
MinRow = MinRow & .Row & ","
MaxRow = MaxRow & (.Row + .Rows.Count - 1) & ","
MinCol = MinCol & .Column & ","
MaxCol = MaxCol & (.Column + .Columns.Count - 1) & ","
End With
Next anArea
With Target.Worksheet.Names
.Add "MinRows", "={" & MinRow & Left(MinRow, Len(MinRow) - 1) & "}"
.Add "MaxRows", "={" & MaxRow & Left(MaxRow, Len(MaxRow) - 1) & "}"
.Add "MinCOls", "={" & MinCol & Left(MinCol, Len(MinCol) - 1) & "}"
.Add "MaxCols", "={" & MaxCol & Left(MaxCol, Len(MaxCol) - 1) & "}"
End With
End Sub

Formulas/VBA macros are copied from some "forum/blog/web" (microsoft.com, excelforum.com, mrexcel.com, stackoverflow.com, tushar-mehta.com, vertex42.com, excelxor.com, trupmexcel.com, excel-easy.com, myonlinetraininghub.com, msofficeforums.com, get-digital-help.com, mbaexcel.com, extendoffice.com, excelhowto.com, ozgrid.com, ecelguru.ca, etc internet network ...). All the merits of the authors mentioned VBA macros.