Highlight active row but after leaving it give its previous color back

Hi

In the attached workbook I have a table to a specific row but after that table I have another section with blue background. I am using the bellow code to highlight the activerow to specific number of columns.

Please Login or Register  to view this content.

PROBLEM: The code is working fine as far the table is concerned. but when my selection goes down in the blue section it overright the color
What I want is to set the color back to the range's previous interior color

Help would be greatly appreciated.


Best Regards
Imran Bhatti

Something like this:

Please Login or Register  to view this content.

Hi XlAdept

Lemme be more clearer
My mistake.

I included the table just to show that the code works on the table but have a deficiency when it comes to ordinary Range . My original workbook does not have a table for some reasons.

Well, I wrote "something like this". The idea is to exit the procedure before reassigning the static range.

Without the actual situation it's difficult to assess the problem

In the range you are wanting this to apply

Are all the cells in the same row all the same colour?

Different rows can be different colours - I am asking about each row separately

xladept and kev thanks for the review

Range("A" & Target.Row & ":I" & Target.Row) all the 9 cells in this row will have only one color but the next row may have different color than the previous one .

Here is a way to do what you have described using conditional formatting (CF)
- CF avoids requirement to capture the existing range colour etc

- one CF rule is used - based on value in column A (which is hidden)
- would probably need to add some VBA to force this CF rule to top priority if you are already using CF

and the VBA:

Please Login or Register  to view this content.

Here is a method NOT using conditional formatting which
- uses 2 named ranges to store previous "row range" and original "colour" information

Test in attached workbook

Please Login or Register  to view this content.

NOTE - do not delete a row or the VBA will crash if the named range reference is lost - I will post an update within the next few days to fix this issue

Hi kev
Intelligent move. Creating separate named ranges is the way to exit sub as xladept said

Originally Posted by xladept

Well, I wrote "something like this". The idea is to exit the procedure before reassigning the static range.

Without the actual situation it's difficult to assess the problem

It worked perfectly.Thanks to all reviewed my problem.

Best Regards
Imran Bhatti

You're welcome!

I looked back at my code and realised I should have used Evauate to make it simpler
And (to simplifyfurther) named range "PreviousRow" value is now the row number" not a range
I suggest you edit named range "PreviousRow" value from a range to the previous row number before you amend the code in your file


working code in attached file

Please Login or Register  to view this content.

BEWARE - if you delete a row it is likely that named range "PreviousRow" may contain incorrect value - I will update later

Hi kev

I had modified your code with Evaluate to make it simpler and now as I visited with a mind to mention that you already did. emmm Telepathy....


Thanks again.

What I want to know is:
- who sent the telepathic message? You or me?

Final revision which handles row deletion by user (see note below *** if you are deleting rows via VBA)

What the code does:
- current "highlight" is removed when a different cell or range is selected
- resets named range values to match active row

If selection is more than one cell:
(this is to prevent issues where rows are deleted)
- named ranges used are reset to a row outside used range
- cell used is A999999 (amend this value to 60,000 if you are using older Excel files where last row was 65,536)
- values are not placed in that cell so it should not increase size of file (not sure with older Excel files )
- you could replace 999999 to a smaller number outside your anticipated used range
- if using more than one file type replace 999999 with something like Rows.Count - 10000

Please Login or Register  to view this content.

*** Deleting rows in VBA
- if you delete or insert rows with VBA then you must deal with impact in VBA
- one way would be to select cell A999999 (with Range("A999999").Select)
- puts active cell outside used range and resets named ranges accordingly
- then disable events (with Application.EnableEvents = False)
- enable events at end of VBA

And here is a very simple way with conditional formatting
- Named Range "ActiveRow" refreshed with active row value
- CF rule AppliesTo A$:I$ , Formula =ROW(A1)=ActiveRow

Please Login or Register  to view this content.

attached workbook also includes code setting CF rule as first priority if required

As your revelations continues , the code is getting better and better. Thank you very much indeed.Now the researchers would find this useful. And be grateful to you....