highlight row and column

[devendra1]

hello everyone. i would like to make reading layout like kutools has. i tried all but I am failed to achieve the desired result. whenever I click a cell respective row and column should be highlighted. i don't want to remove pre existing color formatting s of cell and conditional formatting also. thanks in anticipation . i find its solution nowhere in internet.

 

[breynolds0431]

This is posted at How to auto-highlight row and column of active cell in Excel?

Really easy to do. Just add to sheet module of the worksheet you are wanting the reading layout to be applied.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20200430
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

 

[breynolds0431]

Sorry, I saw that you didn't want the formatting to change as the user moved away from the selected cell. For that, you can use conditional formatting and VBA. I'll try to detail the steps to do that below:

1. Create a Named Range titled HiglightRow by going to Formulas > Named Manager
2. In Name Manager, click New...
3. In the Name: field, enter "HighlightRow"
4. In the Refers to: field, enter "=11"
5. Click OK
6. Follow those steps to add a Named Range named "HighlightColumn" with a Refers to: of "=11"

Next, create some Conditional Formatting - this will retain the cell formats that you've created

1. Select your range on the sheet
2. Home > Conditional Formatting > New Rule
3. Select the "Use a formula to determine which cells to format"
4. Enter "=ROW(A1)=HighlightRow" in the formula field. Then click the Format... button
5. In the Format dialog, set the formatting you'd like to see when you select the cell (e.g.; Fill of Yellow)
6. Click OK to save the condition formatting
7. Follow those steps to create another conditional formatting, this time setting the formula as "=COLUMN(A1) = HighlightColumn" and remember to give it the same formatting as the previous.
8. Click OK to save the changes
9. You can verify the ranges are correct by going to Home > Conditional Formatting > Manage Rules. Ensure the Applies to range encompasses your sheets complete range.

Then, in the sheet's module, add the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightRow")
.Name = "HighlightRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
With ThisWorkbook.Names("HighlightColumn")
          .Name = "HighlightColumn"
          .RefersToR1C1 = "=" & ActiveCell.Column - 1
End With
End Sub

And that should do it. Test by clicking through your sheet. If something isn't working, the formula for the conditional formatting may have been altered by Excel. Go back into Home > Conditional Formatting > Manage Rules and open the Row and Column rules you've created. Ensure the "=COLUMN(A1)" is still A1. Sometimes Excel changes this.

 

[DanteAmor]

Hi @devendra1 and welcome to MrExcel!

In the events of your sheet, put the following:

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

---------------
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Create 2 conditional formatting:
1) conditional formatting.
Formula:

=ROW()=CELL("row")

Format: The color you want.
Applies to: =$1:$1048576 (Or the range you want.)

2) conditional formatting.
Formula:

=COLUMN()=CELL("column")

Format: The color you want.
Applies to: =$1:$1048576 (Or the range you want.)

 

[devendra1]

Sorry, I saw that you didn't want the formatting to change as the user moved away from the selected cell. For that, you can use conditional formatting and VBA. I'll try to detail the steps to do that below:

1. Create a Named Range titled HiglightRow by going to Formulas > Named Manager
2. In Name Manager, click New...
3. In the Name: field, enter "HighlightRow"
4. In the Refers to: field, enter "=11"
5. Click OK
6. Follow those steps to add a Named Range named "HighlightColumn" with a Refers to: of "=11"

Next, create some Conditional Formatting - this will retain the cell formats that you've created

1. Select your range on the sheet
2. Home > Conditional Formatting > New Rule
3. Select the "Use a formula to determine which cells to format"
4. Enter "=ROW(A1)=HighlightRow" in the formula field. Then click the Format... button
5. In the Format dialog, set the formatting you'd like to see when you select the cell (e.g.; Fill of Yellow)
6. Click OK to save the condition formatting
7. Follow those steps to create another conditional formatting, this time setting the formula as "=COLUMN(A1) = HighlightColumn" and remember to give it the same formatting as the previous.
8. Click OK to save the changes
9. You can verify the ranges are correct by going to Home > Conditional Formatting > Manage Rules. Ensure the Applies to range encompasses your sheets complete range.

Then, in the sheet's module, add the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ThisWorkbook.Names("HighlightRow")
.Name = "HighlightRow"
.RefersToR1C1 = "=" & ActiveCell.Row
End With
With ThisWorkbook.Names("HighlightColumn")
          .Name = "HighlightColumn"
          .RefersToR1C1 = "=" & ActiveCell.Column - 1
End With
End Sub

And that should do it. Test by clicking through your sheet. If something isn't working, the formula for the conditional formatting may have been altered by Excel. Go back into Home > Conditional Formatting > Manage Rules and open the Row and Column rules you've created. Ensure the "=COLUMN(A1)" is still A1. Sometimes Excel changes this.

thanks for your kind gestures. i would like to make the code available to all sheets. that's not the problem I have created a class module for that. if I use conditional formatting it will be available only for the formatted sheet.

To resolve this issue , I applied conditional formatting through vba within class module. all works fine but when I open a sheet already having pre existing conditional formatting rules( color alternate row, I do this to check if any error arise or not) , all pre existing rules overlaps and merge with vba conditional formatting.
I think conditional formatting is not the best idea.

thanks for giving your precious time .

 

[devendra1]

Hi @devendra1 and welcome to MrExcel!

In the events of your sheet, put the following:

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

---------------
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Create 2 conditional formatting:
1) conditional formatting.
Formula:

=ROW()=CELL("row")

Format: The color you want.
Applies to: =$1:$1048576 (Or the range you want.)

2) conditional formatting.
Formula:

=COLUMN()=CELL("column")

Format: The color you want.
Applies to: =$1:$1048576 (Or the range you want.)

thanku very much. but this will work only for the formatted sheet . i want it to available for all sheets. i have created class module for that and using conditional formatting within it. it works fine if I don't have any other pre existing conditional rules . but I think conditional formatting is not the best idea as it slows the sheets and manipulates other pre-existing conditinal formatting rules .

thanks for your precious time u given.

 

[DanteAmor]

You didn't mention that you want it on all sheets. But it's easy to fix, just put the event in Thisworkbook.
As for the conditional formats, you must accommodate them to your liking and stop the one you need. Easy.
to make it work in any cell you must select the whole sheet, that has consequences, that's why I commented that you adjust it to a range. Easy.