UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Highlight Active Row In All Sheets, Office 2013    
 
   
billwild
post Sep 7 2019, 07:51 AM
Post#1



Posts: 110
Joined: 4-June 02



I have a workbook with 6 sheets. I would like to be able to easily see which active row I am in for all the sheets.

I used to know this but I've aged since then.

Bill

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
arnelgp
post Sep 7 2019, 09:30 AM
Post#2



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


add code to worksheet_selectionChange event:
CODE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
    Application.Calculate
End If
End Sub


select all rows from a sheet.
add conditional format.

condition:

=CELL("row")=ROW()

ref: https://trumpexcel.com/highlight-active-row-column-excel/
This post has been edited by arnelgp: Sep 7 2019, 09:31 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
DanielPineault
post Sep 7 2019, 09:35 AM
Post#3


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



There are ways to do this with Conditional Formatting, but they require recalculating the cells manually....

I find VBA to be the only true way to handle this, http://www.devhut.net/2019/02/26/excel-vba...ht-current-row/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
arnelgp
post Sep 7 2019, 10:13 AM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


QUOTE
Daniel:
I find VBA to be the only true way to handle this


it will highlight the row, but will also delete any
color formatting on that row.

conditional format will not destroy any colors on the cells.

if you do not want to calculate, replace:
CODE
If Application.CutCopyMode = False Then
    Application.Calculate
End If


with:
CODE
Application.Volatile = True

This post has been edited by arnelgp: Sep 7 2019, 10:18 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
DanielPineault
post Sep 7 2019, 04:04 PM
Post#5


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



QUOTE
it will highlight the row, but will also delete any
color formatting on that row.

Not necessarily true. It preserves conditional formatting just fine and, if done properly, such a procedure can easily stores the row color(s) and resets it/them when you change rows. The link is for demonstrative purposes and obviously can be adapted to suit individual needs/situations.

One way or another, it is an option that works just fine in many scenarios. The choice is up to the user.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
billwild
post Sep 8 2019, 11:00 AM
Post#6



Posts: 110
Joined: 4-June 02



I tried this as well as the previous suggestion and neither works. What could I being doing wrong. I added them through the Visual Basic

--------------------
always learning....<img src="/forums/images/graemlins/laugh.gif" alt="" />
Go to the top of the page
 
arnelgp
post Sep 8 2019, 01:23 PM
Post#7



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


here is a sample.
Attached File(s)
Attached File  sampleHighlight.zip ( 15.22K )Number of downloads: 5
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
isladogs
post Sep 8 2019, 03:14 PM
Post#8


UtterAccess VIP
Posts: 1,806
Joined: 4-June 18
From: Somerset, UK


Just tested arnel's solution and it works for me.
I also added additional conditional formatting and it was preserved during and after highlighting the row!

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 02:45 AM