Need another solution for this function of highlighting of rows and cells

[Tirrazo]

Hi,


Attched is my workbook.
There is a VBA + a formula to highlight columns and rows.

After a while this has started to make Excel and the workbook slow. As soon as I removed the formula from the formatting rules, the workbook is fast again.

Is there another solution for this to work?

Existing:

VBA:

Please Login or Register  to view this content.

Formula:
=ELLER(CELLE("col")=KOLONNE();CELLE("row")=RAD()) (In Norwegian)

Regards

[Richard Buttrey]

Hi,

Clear all the conditional formats and use the Worksheet_SelectionChange event to identify the relevant row range and column range that needs a background colour and set the .Interior.ColorIndex property to say 15

Start the macro by clearing the background colour from the whole of live range.

[Tirrazo]

Hi,

Thank you for the fast reply.
I am not quite sure what you mean because someone else made this for me long ago.

He made the formula to wotk with the VBA for visualizing the row and column.
This seems to take a lot of process or memory.

I got this VBA without conditional formatting, but this macro removes the color of everything.
I don't want that.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

This macro stores the last selected cell in A1 so that the cell referred to can be cleared next time a cell is selected, and will then paint the row and column of the cell selected in grey.
Obviously the A1 cell could be anywhere.

Please Login or Register  to view this content.

[Tirrazo]

Hi,

Thank you.

Does it require conditional formatting?
I can't make that VBA work. Nothing happens.

Can you upload a workbook with that VBA?

[Richard Buttrey]

Hi,

See the attached workbook.

No, conditional formatting is not required to highlight the row/column selected. As I said Cond. Formats do tend to slow things down and there were a lot in your original workbook.

You may have other cells which require CF's but try to minimise them.

[Tirrazo]

I have tried it now, but when I try to mark up some rows to change the color, I'm getting this error.

Still, I feel that the response is not fast enough. I work very much back and forward so I use the arrow keys a lot.
If I press the right arrow key and intend to fast forward to the right side of the sheet and then releases, the page won't stop but continues several cells to the right after I have released the arrow key.

[Richard Buttrey]

To allow you to select more than one cell without the macro firing you could add an initial line of code

If Target.Cells.Count > 1 Then Exit Sub

However I fear there is little you can do to overcome the response time when scrolling with the arrow keys. It's just a fact of life that VBA needs to communicate with the sheet and there's a time overhead in doing that.

You could use the Sheet_BeforeDoubleClick event rather than the sheet_selection event. That would only fire when you double clicked a cell

[Tirrazo]

Hi,

Thank you.

I testet to add this line, still getting the debug :o

[Artik]

Try to change to:

Please Login or Register  to view this content.

Artik

[Tirrazo]

Hi,

Thank you.
But it's still not working.

You can see I am marking some cells and the debug appears.

[Richard Buttrey]

Place the
If Target.Cells.Count > 1 Then Exit Sub

before the

If Target = Range("A1") Then Exit Sub

[protonLeah]

ThisWorkbook module

Please Login or Register  to view this content.

[Tirrazo]

Hi,

Thank you very much.

I can see this solution contains 2 conditional formatting formulas. I tried to change the color from blue to gray in the formatting rule, but that doesn't work.
Must the color be changed in the VBA?

And the marking of columns and rows are going forever and maybe takes up extra memory, how to set limits for how long the highlight will work?

After I activate the macro, the blue cross disappears.

Regards

[protonLeah]

Some slight mods and additions,
For grey highlight the color enum needs an additional item:

Please Login or Register  to view this content.

Modified the row/column enum to add option to highlight only the cells ABOVE the active cell and only the cells to the LEFT of the active cell. Also changed to more descriptive names.

Please Login or Register  to view this content.

---

To select the cells to be highlighted, delete the highlighted code as below press the period key and select from the dropdown that appears

HighLightSpan = Span.RowToCellColToCell

---

Please Login or Register  to view this content.

[Tirrazo]

Hi,

Thank you for this.
I have problems making the macro work.
As soon as I activate the macro, the highlighting is gone. Like the VBA stops working.


Regards

[Tirrazo]

Place the
If Target.Cells.Count > 1 Then Exit Sub

before the

If Target = Range("A1") Then Exit Sub

I am trying to copy the VBA to another sheet and another workbbok. But when I save the VBA after pasting the code, it wont work.

[protonLeah]

Hi, ...
I have problems making the macro work.
As soon as I activate the macro, the highlighting is gone. Like the VBA stops working. ...

I don't know who you are responding to.
If regarding my code, then I don't know what "... activate the macro..." means. You don't activate the macro, it's auto-triggered anytime you select a new cell on any sheet. The code does not belong in the worksheet modules. You should have copy/pasted it to the THISWORKBOOK module.

[Tirrazo]

I don't know who you are responding to.
If regarding my code, then I don't know what "... activate the macro..." means. You don't activate the macro, it's auto-triggered anytime you select a new cell on any sheet. The code does not belong in the worksheet modules. You should have copy/pasted it to the THISWORKBOOK module.

Hi,

I am sorry, should have replied with quote.

Excel has this yellow warning whenever I open a Macro enabled workbook.
It says the macro is deactivated and I have to press the "activate" button for the macro to work.

[protonLeah]

Try:
File
* Options
** Trust Center
*** Trust Center Settings > Macro Settings
**** Enable all macros & Trust access to the VBA project object model

[Tirrazo]

Try:
File
* Options
** Trust Center
*** Trust Center Settings > Macro Settings
**** Enable all macros & Trust access to the VBA project object model

Thank you.
Now I don't have to activate it, but it still says the file is unsafe.

But now I got this error, see attached photo.
After I open the file and move the marker, the highlighting disappear. Like there is something that disables that function to work.

[protonLeah]

I can't duplicate the error with the workbook I posted; and, I don't know how your actual workbook is different from it. It even worked if more than one cell is selected if I comment out the first line (...countlarge...)

[Tirrazo]

I can't duplicate the error with the workbook I posted; and, I don't know how your actual workbook is different from it. It even worked if more than one cell is selected if I comment out the first line (...countlarge...)

Could you try to implement the code in this workbook and see if it works better for me?

Could you also set the highlight to vertically go down to row 300 and horisontally go right to column ED.

[protonLeah]

The workbook you attached in post #23 has the Worksheet_SelectionChange code:

Please Login or Register  to view this content.

It does not contain the code that I provided, so I don't know if you want to use it or not. However, you can't/shouldn't have both the
Worksheet_SelectionChange code and the Workbook_SheetSelectionChange code trying to operate on the same sheet as is.

---

Notice that A1 is empty when the worksheet is first activated. If you select a cell that's also empty, the code exits. However, if you select any cell with data, the line:
With Range(Range("A1"))
is executed. This line expects A1 to contain the address of the last selected cell but A1 is empty so the line is evaluated as: With Range(""). Resulting in an error crash.

You may modify the code as such:

Please Login or Register  to view this content.

---

My code has the option of entire row/entire column and I don't think there would be any advantage to limit the rows to 300 and the columns to ??

[Tirrazo]

The workbook you attached in post #23 has the Worksheet_SelectionChange code:

Please Login or Register  to view this content.

It does not contain the code that I provided, so I don't know if you want to use it or not. However, you can't/shouldn't have both the
Worksheet_SelectionChange code and the Workbook_SheetSelectionChange code trying to operate on the same sheet as is.

---

Notice that A1 is empty when the worksheet is first activated. If you select a cell that's also empty, the code exits. However, if you select any cell with data, the line:
With Range(Range("A1"))
is executed. This line expects A1 to contain the address of the last selected cell but A1 is empty so the line is evaluated as: With Range(""). Resulting in an error crash.

You may modify the code as such:

Please Login or Register  to view this content.

---

My code has the option of entire row/entire column and I don't think there would be any advantage to limit the rows to 300 and the columns to ??

Thank you very much for the response.

Your code is very complex and I would like to try out both versions.

What is the different about Worksheet and Workbook selection change?

The workbook you sent to me, the last one: "highlight active row and or column (1)"
It's highlighting only a small area. This could be extended to a larger area.

Attached also snippet where you see the yellow marked text comes after the debug message comes.

Please Login or Register  to view this content.

I know this is not your code, but it seems to work and not bring any slowdown. The only problem is that it removes every color a cell might have in my sheet.
Is that possible to getaround?

[Tirrazo]

Hi,


Kindly see attached document.

I got a short VBA code that does what I need it to do (highlight columns and rows after where I move in the sheet).

The only problem: I can't use any colors because the code destroys them.

How to solve this?


The original spreadsheet contains different colors that each refers to a part of a project.
Since the sheet will be loaded by numbers, it's difficult to see which row I am at.
The numbers are gone because it's confidential.


Regards.

[Richard Buttrey]

...for the record this was originally posted as a new thread. In that thread which is now closed I added

Your request seems contradictory. On the one hand you want the whole of a column and row to be highlighted as you move, yet you also want to paint a range of cells with a colour and have that colour remain even when you move to a cell in the previously coloured range. The grey highlighting is just another colour.

The only way I could conceive of doing this is to have another test in the macro to determine if any of the cells in a column you move to are already coloured, and in which case exit the macro. However I'll leave you to decide whether that's the sort of functionality you want and comment here.

[Tirrazo]

There was no more replies so I thought people got lost in the communication. So I created a new thread with the code I felt worked best as a start.

Could you have another look with my comments taken in consideration?

I can send it to you by someof the numbers and the sections of colors, but I have to take away some of the numbers.

The point for me was if It worked in this clean version it would work in the original one.

The highlighting as a cross of column/row will be a tool to better show me where I'm at when going sideways since I've frozen a part of it at the left side.

[Richard Buttrey]

There was no more replies so I thought people got lost in the communication. So I created a new thread with the code I felt worked best as a start.

Could you have another look with my comments taken in consideration?

I can send it to you by someof the numbers and the sections of colors, but I have to take away some of the numbers.

The point for me was if It worked in this clean version it would work in the original one.

The highlighting as a cross of column/row will be a tool to better show me where I'm at when going sideways since I've frozen a part of it at the left side.

That doesn't actually indicate whether the potential solution I suggested would be of any use. i.e. any coloured cells in a row or column would to which you moved wouldn't have the column/row highlighted.

There's also the potential effect of the macro having to check each cell in a row/column to see if it's coloured. The original problem you were trying to address was the slowness. The more VBA code you have which has to interrogate each cell the slower will the system become.

[protonLeah]

Modified from post #3. Put in the Thisworkbook module. Do not copy to any sheet modules...

Please Login or Register  to view this content.

[Tirrazo]

Modified from post #3. Put in the Thisworkbook module. Do not copy to any sheet modules...

Please Login or Register  to view this content.

Hi,
Thank you!

I posted it in thisworkbook but nothing happens ;/

[protonLeah]

Well, I don't know what else I can do. It works on the sample wb you provided in the range you specified in post #3, i.e., D9:DV512

[Tirrazo]

Well, I don't know what else I can do. It works on the sample wb you provided in the range you specified in post #3, i.e., D9:DV512

Thank you very much!

I have now figured out that there is a problem with my Excel itself;

My normal computer has Excel version: 2203 The VBA highlights row/column immediately after opening. As soon as I move from the cell you saved the sheet, the highligh is gone and don't come back.
Second computer Excel version: 2204 The VBA highlights row/column immediately after opening. As soon as I move from the cell you saved the sheet, the highligh is gone and don't come back.
Work PC Excel version: 2102 The VBA highlighting works fine inside the area you defined.

So what is the problem with Excel that doesn't let me even try out the workbook?


Regards

[Tirrazo]

Is it possible instead of highlighting the row and column to make the VBA draw a horisontal/vertical line from where the marked cell is?

I don't know how to revert my excel version and I am struggling to make some of the VBA's work with the highlighting function.

Should I reinstall office and try again, can something block the VBA from doing it's purpose?
I am getting no error message, it just doesn't highlight anything at all.