Skip to main content

How to auto-highlight row and column of active cell in Excel?

When you view a large worksheet with numerous data, you may want to highlight the selected cell’ row and column so that you can easily and intuitively read the data to avoid misreading them. Here, I can introduce you some interesting tricks to highlight the row and column of the current cell, when the cell is changed, the column and row of the new cell are highlighted automatically as following screenshots:

Auto-highlight row and column of selected cell with VBA code
One click to highlight row and column of selected cell

Auto-highlight row and column of selected cell with VBA code

The following VBA code can help you automatically highlight the entire column and row of the current cell in worksheet, please do with following steps:

1. Open the worksheet you will auto-highlight the row and column of active cell, right click the sheet tab and select View Code from the context menu.

2. Then copy and paste following VBA code into the blank code window:

VBA code: auto-highlight row and column of selected cell

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

3. Then press the Alt + Q keys together to return to the worksheet, now when you select a cell, the entire row and column of this cell has been highlighted. And it will be dynamically moved as the selected cell changes.

doc-reading-layout4

Notes:

  • 1. In the above code, you can change .ColorIndex = 6 color to other color you like.
  • 2. This VBA code only apply to the worksheet you choose in the above step 2.
  • 3. If you do not want to highlight the column and row of the selected cell again, you need to delete the above VBA and set no fill to the selected row and column by clicking Home > Fill color > No fill.
  • 4. If there are some colored cells in your worksheet, the color will be lost when you click the cell and then move to other cell.

Auto-highlight row and column of selected cell with Kutools for Excel

If you are a newbie of VBA, here recommend the Kutools for Excel's Reading Layout utility. This utility helps to easily highlight row and column of selected cell in Excel as the below demo shown. Download and try it now! (30-day free trail)

Let's see how to apply the Kutools for Excel’s Reading Layout feature to highlight entire rows and columns of a range of cells.

1. Click Kutools > Reading Layout. See screenshot:

Then the reading layout is enabled, you can see the row and column of active cell are highlighted immediately. And it will be dynamically moved as the selected cells change.

Notes:

  • 1. You can change the reading layout settings based on your needs as below screenshot shown.
  • 2. You can cancel this utility by unchecking the Reading Layout under Reading drop down list.
  • 3. This Reading Layout View feature is applied to all of the worksheets within your workbook.
  • 4. This Reading Layout View feature will disabled when you launch the workbook next time.

One click to highlight row and column of selected cell

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (61)
Rated 5 out of 5 · 1 ratings
dreams knight
dreams knight
This comment was minimized by the moderator on the site
And please reply to an email
Reply
0
0
dreams knight
dreams knight
This comment was minimized by the moderator on the site
Great job . But in the first task at the top of the browser page, when I did it, it was working correctly, but when I made protection for the sheet, it stopped working correctly, you see, what's the solution. Thank you
Reply
0
0
skyyang
skyyang     dreams knight
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please apply the followign code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rowRange As Range
    Dim colRange As Range
    Dim activeCell As Range
    Me.Unprotect
    Cells.Interior.ColorIndex = xlNone
    Set activeCell = Target.Cells(1, 1)
    Set rowRange = Rows(activeCell.Row)
    Set colRange = Columns(activeCell.Column)
    rowRange.Interior.Color = RGB(173, 233, 249)
    colRange.Interior.Color = RGB(173, 233, 249)
    Me.Protect
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
sahudewa@gmail.com
This comment was minimized by the moderator on the site 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    
    ' Clear previous highlighting
    Me.Cells.FormatConditions.Delete
    
    ' Check if Checkbox1 is checked
    ' If CheckBox1.Value = True Then
        ' Check if a single cell or a range of cells is selected
        If Target.Cells.Count = 1 Or Target.Cells.Count > 1 Then
            Dim selectedRange As Range
            Dim selectedRows As Range
            Dim selectedColumns As Range
            
            Set selectedRange = Target
            Set selectedRows = Me.Range(Me.Cells(selectedRange.Row, 1), Me.Cells(selectedRange.Row + selectedRange.Rows.Count - 1, 1).EntireRow)
            Set selectedColumns = Me.Range(Me.Cells(1, selectedRange.Column), Me.Cells(1, selectedRange.Column + selectedRange.Columns.Count - 1).EntireColumn)
            
            ' Add conditional formatting to highlight the selected rows
            With selectedRows.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
                .Interior.Color = RGB(255, 230, 153) ' Yellow color
            End With
            
            ' Add conditional formatting to highlight the selected columns
            With selectedColumns.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
                .Interior.Color = RGB(255, 230, 153) ' Yellow color
            End With
        End If
    ' End If
    
    On Error GoTo 0
End Sub
Reply
Report
0
0
sahudewa@gmail.com
This comment was minimized by the moderator on the site
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

' Clear previous highlighting
Me.Cells.FormatConditions.Delete

' Check if a single cell or a range of cells is selected
If Target.Cells.Count = 1 Or Target.Cells.Count > 1 Then
Dim selectedRange As Range
Dim selectedRows As Range
Dim selectedColumns As Range

Set selectedRange = Target
Set selectedRows = Me.Range(Me.Cells(selectedRange.Row, 1), Me.Cells(selectedRange.Row + selectedRange.Rows.Count - 1, 1).EntireRow)
Set selectedColumns = Me.Range(Me.Cells(1, selectedRange.Column), Me.Cells(1, selectedRange.Column + selectedRange.Columns.Count - 1).EntireColumn)

' Add conditional formatting to highlight the selected rows
With selectedRows.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
.Interior.Color = RGB(255, 230, 153) ' Yellow color
End With

' Add conditional formatting to highlight the selected columns
With selectedColumns.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
.Interior.Color = RGB(255, 230, 153) ' Yellow color
End With
End If

On Error GoTo 0
End Sub
Reply
Report
0
0
Disgruntled Researcher
Disgruntled Researcher
This comment was minimized by the moderator on the site
can you please post code in VBA format? trying to figure out what your code looks like from what you posted in a jumble is frustrating.
Reply
0
0
A. Patel
A. Patel
This comment was minimized by the moderator on the site
Hi,

The above code is brilliant. But i have two questions and if you can help then will be much appreciated. Let me know if there is cost associated with it.

1. The above code is removing my previous formatting of the cells. Eg. if i have got the cells filld with blue then above code clears that fill.
2. Is there a code that i can use when i am extracting values from another sheet and that row in the that sheet gets highlighted as well. Eg. I want to extract vale from another cell, so when i press = sign in sheet 1 and then when i move to sheet 2 to bring the value the row in the sheet doesn't get highlighted.

Regards,
Reply
0
0
skyyang
skyyang     A. Patel
This comment was minimized by the moderator on the site
Hello, Patel
For the first question:
It may be difficult to modify the VBA code, so, I recommend you to apply the Conditional Formatting feature for solving this problem, the following article may help you:
https://www.extendoffice.com/documents/excel/3500-excel-highlight-selected-row-conditional-formatting.html

Or you can use the Kutools for Excel' Reading Layout feature, you can try it 30 days for free.
Please download from: https://www.extendoffice.com/download/kutools-for-excel.html

For the second question:
Sorry, I can't solve this problem at present,😜
Reply
Report
0
0
2Took
2Took
This comment was minimized by the moderator on the site
How do you modify the code so that:
1. It does not permanently strip existing colors of cells. Currently, after another cell is selected, it leaves colorless previous row. I'd like to keep the colors of the worksheet, as they were before row was highlighted by this code.
2. Limit the highlight width to only Columns A thru K, not the entire row.
Reply
0
0
skyyang
skyyang     2Took
This comment was minimized by the moderator on the site
Hello,
It may be difficult to modify the VBA code, so, here, I recommend you to apply the Conditional Formatting feature for solving this problem, please view the below article: (Note: if you need to highlight the column A to column K, you just need to select the range you need, and then apply the conditional formatting.)
https://www.extendoffice.com/documents/excel/3500-excel-highlight-selected-row-conditional-formatting.html


Or you can use the Kutools for Excel' Reading Layout feature, you can try it 30 days for free.
Please download from: https://www.extendoffice.com/download/kutools-for-excel.html

Please try, hope it can help you!
Reply
Report
0
0
Jerry
Jerry
This comment was minimized by the moderator on the site
Thank you so much for this tip!!!
Reply
0
0
Rated 5 out of 5
Rodney
Rodney
This comment was minimized by the moderator on the site
Another easy way to highlight your rows: https://youtu.be/iOF06mZDGIk
Reply
0
0
Gues
Gues
This comment was minimized by the moderator on the site
FAIL. Wiped out all my other cell formatting irreversibly. And doesn't work if the sheet is protected. FAIL.
Reply
0
0
crystal
crystal     Gues
This comment was minimized by the moderator on the site
Hi Gues,The code does not work in a protected worksheet. If you want to keep the original cell formatting, please try the below code.The Reading Layout feature of Kutools is stable and handy, maybe you can download a free trial to have a try.
<div data-tag="code">Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 202001106
Static xRow
Static xColumn

Dim xWs As Worksheet
Dim xCRg, xCRg1, xRRg, xRRg1, xOHRg As Range
Dim xAHRORg, xAHRORg1 As Range
Dim xAWs As Worksheet
Dim xFNum As Integer
Dim xCll As Range

On Error Resume Next

Application.ScreenUpdating = False
pRow = Selection.Row
pColumn = Selection.Column

Set xWs = ActiveWorkbook.Worksheets.Item("AutoHighlightRAndC")
Set xAWs = Target.Worksheet
If xWs Is Nothing Then
Set xWs = ActiveWorkbook.Worksheets.Add
xWs.Name = "AutoHighlightRAndC"
xWs.Visible = xlSheetHidden
xAWs.Activate
End If

If xColumn <> "" Then
Set xCRg = Columns(xColumn)
xCRg.Interior.ColorIndex = xlNone
Set xCRg = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xCRg.Count
Set xAHRORg1 = xAWs.Cells(xFNum, xColumn)
Set xAHRORg = xWs.Cells(xFNum, xColumn)
xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex
'xCRg.Item(xFNum).Interior.ColorIndex = xWs.Columns(xColumn).Item(xFNum).Interior.ColorIndex
Next

Set xRRg = Rows(xRow)
xRRg.Interior.ColorIndex = xlNone
Set xRRg = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xRRg.Count
Set xAHRORg1 = xAWs.Cells(xRow, xFNum)
Set xAHRORg = xWs.Cells(xRow, xFNum)
xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex
'xRRg.Item(xFNum).Interior.ColorIndex = xWs.Rows(xRow).Item(xFNum).Interior.ColorIndex
Next

End If

xRow = pRow
xColumn = pColumn

Set xCRg = Columns(pColumn)
Set xCRg1 = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xCRg1.Count
Set xOHRg = xWs.Cells(xFNum, pColumn)
xOHRg.Interior.ColorIndex = xCRg1.Item(xFNum).Interior.ColorIndex
Next

Set xRRg = Rows(pRow)
Set xRRg1 = Intersect(xRRg.Worksheet.UsedRange, xRRg)

For xFNum = 1 To xRRg1.Count
Set xCll = xWs.Cells(pRow, xFNum)
xCll.Interior.ColorIndex = xRRg1.Item(xFNum).Interior.ColorIndex
Next
xCRg.Interior.ColorIndex = 34
xRRg.Interior.ColorIndex = 34
Application.ScreenUpdating = True
End Sub
Reply
Report
0
0
raguirre6776@gmail.com
This comment was minimized by the moderator on the site
The Reading Layout button is grayed out and I cannot select. What am I missing here?
Reply
Report
0
0
crystal
crystal    
This comment was minimized by the moderator on the site
Hi,
Is the Worksheet Design turning on? If the Worksheet Design is turning on, the Reading View will turn gray. You need to turn it off to activate the Reading Layout.
Reply
Report
0
0
robert white
robert white
This comment was minimized by the moderator on the site
forgive me, i am a novice, how do i run this code in my worksheet?
Reply
0
0
crystal
crystal     robert white
This comment was minimized by the moderator on the site
Hi,

Right click the sheet tab and select View Code from the context menu. Copy and paste the code into the Sheet(Code) window. Go back to the worksheet and the code will run automatically.
View Attachments (1 / 5)
Reply
Report
0
0
Shaikh Wasim
Shaikh Wasim
This comment was minimized by the moderator on the site
When i try the above code to highlight row & column, its nt working, also i tried another code which worked as i found it in below comments which is

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.ColorIndex = 22
Target.EntireColumn.Interior.ColorIndex = 22

it worked but cleared all my cell colors to white & highlight the active cell, Please help me on this @crystal
Reply
0
0
crystal
crystal     Shaikh Wasim
This comment was minimized by the moderator on the site
Hi Shaikh Wasim,
The VBA code in the post works well in my case. Which Excel version do you use? As the "color cleared" problem you memtioned, I can't help to solve it yet. Way not try the Reading Layout utility of Kutools for Excel? It won't make any changes to your worksheet. Thanks for commenting.
Reply
Report
0
0
Alok Ghosh
Alok Ghosh
This comment was minimized by the moderator on the site
After pasting the VBA code I saved as Add-In but not working. I want to change the color of the intersection cell also...
Reply
0
0
crystal
crystal     Alok Ghosh
This comment was minimized by the moderator on the site
Good Day,
You can change the highlight color by changing the .ColorIndex value. But the color of the intersection cell can't be changed alone.
Reply
Report
0
0
Shushka
Shushka
This comment was minimized by the moderator on the site
Above VBA code did not work. I've tried the below code for highlighting both the selected column and row. It's shorter and easier



Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.ColorIndex = 22
Target.EntireColumn.Interior.ColorIndex = 22


End Sub
Reply
Report
0
0
crystal
crystal     Shushka
This comment was minimized by the moderator on the site
Dear Urska,
Thank you for sharing.
Reply
Report
0
0
D
D
This comment was minimized by the moderator on the site
How can I change the VBA so the text color changes when the row is highlighted?



Also, if you want to just highlight a row without the column.. Here is the code changes.



Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318 'Updated By dlsodders for
'Select Just Row
Static xRow

If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
pRow = Selection.Row

xRow = pRow

With Rows(pRow).Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
End Sub
Reply
0
0
crystal
crystal     D
This comment was minimized by the moderator on the site
Good Day,
The following code can help you change the text color when the row is highlighted.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
Static xCol
Dim pRow As Long
Dim pCol As Long
If Target.Count = 1 Then
If xRow <> "" Then
With Rows(xRow)
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
End If
pRow = Selection.Row
pCol = Selection.Font.ColorIndex
xRow = pRow
xCol = pCol
With Rows(pRow)
.Interior.ColorIndex = 10
.Interior.Pattern = xlSolid
.Font.Color = -16776961
End With
End If
End Sub
Reply
Report
0
0
David
David
This comment was minimized by the moderator on the site
Thank you for posting this! It's very useful in check reconciliation via spreadsheet.
Reply
0
0
Jay
Jay
This comment was minimized by the moderator on the site
The VBA to highlight a single cell works but this one does not.
Reply
0
0
nolan
nolan
This comment was minimized by the moderator on the site
how do you prevent the undo & redo buttons from being deleted with this macro not using the Kutools
Reply
0
0
crystal
crystal     nolan
This comment was minimized by the moderator on the site
Good Day,
Sorry about that. The undo & redo problem in the macro can't be solved.
Reply
Report
0
0
David
David
This comment was minimized by the moderator on the site
I changed your code from highlighting the interior to making red borders.
.BorderAround Weight:=xlThick, Color:=vbRed
I select a cell, the borders turns red. Great. I copy, select another cell, those borders turn red, but the clipboard clears. I cannot figure out how to use borders, and keep the copy/paste functionality.
Any suggestions?
Reply
0
0
astollie@gmail.com
    David
This comment was minimized by the moderator on the site
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

'Update 20192021
Static xRow
If xRow <> "" Then
Rows(xRow).BorderAround Weight:=xlThin, Color:=vbBlack
End If
pRow = Selection.Row
xRow = pRow
Rows(pRow).BorderAround Weight:=xlThick, Color:=vbRed
End Sub
Reply
Report
0
0
Char
Char     David
This comment was minimized by the moderator on the site
can you share the whole code?
Reply
0
0
Chienph
Chienph
This comment was minimized by the moderator on the site
Hi. How can i high light entire row if one of cell is merge. I see kutool can make a line across a merge cell. Pl help me this solution
Reply
0
0
Vicente
Vicente
This comment was minimized by the moderator on the site
Great article!. Life saver for me. And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 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 = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End Sub
Reply
0
0
Kamran
Kamran     Vicente
This comment was minimized by the moderator on the site
Correct code as follows: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 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 = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End Sub [quote]Great article!. Life saver for me. And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20151216 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 = 15 .Pattern = xlSolid End With With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With Selection.Interior.ColorIndex = 6 End SubBy Vicente[/quote]
Reply
0
0
KSV
KSV
This comment was minimized by the moderator on the site
HOW CAN I MAKE IT POSSIBLE IF HAVE SHEET THAT IS ALREADY COLORED.
Reply
0
0
mike_thomas
mike_thomas
This comment was minimized by the moderator on the site
This works if you don't have colors not in conditional formatting. Just add two more with loops. Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow Static xColumn If xColumn "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 6 .Pattern = xlSolid End With With Rows(pRow - 1).Interior .ColorIndex = 0 .Pattern = x1Solid End With With Rows(pRow + 1).Interior .ColorIndex = 0 .Pattern = x1Solid End With End Sub
Reply
0
0
SaltWater
SaltWater
This comment was minimized by the moderator on the site
When I reopen my workbook highlighted column from the prior session remains - and does change as I move around the worksheet in a new session. Is there a way to "clear" the prior session each time the workbook is opened?
Reply
0
0
Aditya
Aditya
This comment was minimized by the moderator on the site
Bravo..exactly what I was looking for.....thanks a lot!!
Reply
0
0
listermoss
listermoss
This comment was minimized by the moderator on the site
This is the code I used for just the row (grey highlight): Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow If xRow "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 15 .Pattern = xlSolid End With End Sub
Reply
0
0
Christina
Christina     listermoss
This comment was minimized by the moderator on the site
Copying and pasting your code did not work properly, but after fixing the syntax errors it worked amazingly! Thank you. Here is the working code for highlighting (yellow) the row only: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 Static xRow If xRow "" Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If pRow = Selection.Row xRow = pRow With Rows(pRow).Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub
Reply
0
0
betakos
betakos
This comment was minimized by the moderator on the site
how can i stop it if i want it to stop??
Reply
0
0
Ton Hermes
Ton Hermes
This comment was minimized by the moderator on the site
How can I change the colors of the bars? Thnaks for this great solution. Regards, Ton
Reply
0
0
Mac
Mac
This comment was minimized by the moderator on the site
Any plans to add Mac functionality for Kutools?
Reply
0
0
Charles
Charles
This comment was minimized by the moderator on the site
To keep your cell color you will have to create a Conditional Formatting for the cells.
Reply
0
0
Will
Will
This comment was minimized by the moderator on the site
Just what I was looking for. Thank you!
Reply
0
0
vipe
vipe
This comment was minimized by the moderator on the site
This works brilliantly. Is there a way of making it not show up on printing?
Reply
0
0
Matthewwwww
Matthewwwww
This comment was minimized by the moderator on the site
Hi! I really like this code but like Suresh said, it removes original cell colors! How can I keep my original cell colors?? Please respond, thanks!
Reply
0
0
felix
felix
This comment was minimized by the moderator on the site
Hi, after putting in the code, the "undo" function no longer works. Can someone help?
Reply
0
0
Suresh Singh
Suresh Singh
This comment was minimized by the moderator on the site
Hi, the given code works fine for highlighting the row and coloumn of active cell. but the original cell colour along with coloumn and rows colour settings are lost. How to overcome this?
Reply
0
0
Janet D
Janet D     Suresh Singh
This comment was minimized by the moderator on the site
This option is now available to non-coders in 2010 as long as you have the Developer tab. Under Developer, click Properties. Set EnableOutlining to True. Limitation: This appears to only apply to one worksheet at a time, although easy to set for other worksheets by clicking on tabs at bottom (while leaving the Properties dialog box open). Why Excel does not make this available under Options, well, it is Microsoft.
Reply
0
0
betakos
betakos     Suresh Singh
This comment was minimized by the moderator on the site
i use insert table to my data and it does not change original colouring
Reply
0
0
RaveFromDave
RaveFromDave     Suresh Singh
This comment was minimized by the moderator on the site
Here's my version for highlighting the current row (from A to AK). To avoid wiping out cell colouring I use a red border instead...plus it restores my preferred border to the row just left. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lRow As Long, lCol As Long Static lPrevRow As Long 'Restore previous active row to...light blue dotted line If lPrevRow > 0 Then Range("A" & lPrevRow, "AK" & lPrevRow).BorderAround Weight:=xlHairline, Color:=RGB(162, 200, 255) End If 'Highlight current row with a red border lRow = Target.Row Range("A" & lRow, "AK" & lRow).BorderAround Weight:=xlThin, Color:=RGB(255, 0, 0) lPrevRow = lRow End Sub
Reply
0
0
peenkwan
peenkwan     RaveFromDave
This comment was minimized by the moderator on the site
This is the perfect code thank you!!!!! no problems with moving from cell to cell and leaving a track of highlights and outlines behind.
Reply
0
0
Ka Yen
Ka Yen     RaveFromDave
This comment was minimized by the moderator on the site
thank you!
Reply
0
0
Randy
Randy     RaveFromDave
This comment was minimized by the moderator on the site
Could not get this to work - please help, thanks :-)
Reply
0
0
Bozo
Bozo     Randy
This comment was minimized by the moderator on the site
"Please help" - do you expect an answer with such a vague request, and no effort to explain the problem?
Reply
0
0
MARIO
MARIO     RaveFromDave
This comment was minimized by the moderator on the site
HI I HAVE THE SAME PROBLEM WITH SURESH CAN YOU PLEASE PROVIDE ME WITH THE WHOLE AMENDED CODE FOR NOT WIPING THE ORIGINAL CELL COLOUR I'M USING THIS: Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) 'Update 20140318 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
Reply
0
0
calixco
calixco     RaveFromDave
This comment was minimized by the moderator on the site
you are a geniuos now, how abot rows and columsn?
Reply
0
0
farfan
farfan     RaveFromDave
This comment was minimized by the moderator on the site
Oh! man, works like a charm!!! great
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations