Solved Auto Highlight Selected Rows - VBA & Conditional Formatting

Microsoft Excel 2010 - complete product...
September 4, 2018 at 03:56:04
Specs: Windows 7 x64, 2,4 GHz / 4 GB
In a database with over 10000 rows it's hard to follow each row of data so I was wondering if there's a way to highlight them, but without "damaging" the formatting of the existing cells (fill, font color).

I found a way thanks to the following link that uses conditional formatting and VBA:
http://www.thesmallman.com/blog/201...

To summarize:
- select entire table of data, add a new rule in conditional formatting, use a formula to determine which cells to format and insert the following formula:

=OR(CELL("row")=CELL("row";A1))

- right click on the sheet you are planning to use auto highlight on, select View Code and in there insert the following snippet:

Option Explicit

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

I know conditional formatting is volatile and with the increase of data amount it will work slower, but I only need it for up to 10k rows so it's OK.

What I was further wondering is:
Is it possible to adapt the formula and snippet so that if I select 3 or 6 or 19 etc. cells, it highlights all 3 / 6 / 19 etc. rows in the table?

Thanks in advance for any suggestion.

message edited by Mrrrr


See More: Auto Highlight Selected Rows - VBA & Conditional Formatting

Reply ↓  Report •

✔ Best Answer
November 19, 2018 at 06:08:54
Look at the second to last solution found at the link below. (Title: Advanced Technique) The one that uses the SUMPRODUCT function in the Conditional Formatting formula.

Eliminate the part of the CF formula that deals with the columns, unless you want that feature also.

You can probably also clean up the code if you don't need the Columns highlighted. I didn't bother as simply changing the CF formula eliminated the column highlighting.

http://www.tushar-mehta.com/publish...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
September 4, 2018 at 09:49:32
Does the table start in A1?

If not, do the rows in the table start in Column A or some other Column?

IOW, which one of these (more or less) do you have? All of these tables are the same size, they just start and end in different rows and/or columns. Different options exist for each situation.

A1:Z10000
A5:Z10004
C8:AB10007

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 5, 2018 at 22:00:17
They all start in A1 and last for 10000 rows. Or more rows and columns, but always starting in A1.

A1:Z10000 is just fine.

message edited by Mrrrr


Reply ↓  Report •

#3
November 19, 2018 at 04:50:45
Sorry to bump this, but is there any possibility to do it?

Also I am guessing that highlighting the limits of a selection (for example making red borders to a selection of cells) has the same prerequisites.

I haven't found a way to solve this and would like to close this question even there is no resolution.


Reply ↓  Report •

Related Solutions

#4
November 19, 2018 at 06:08:54
✔ Best Answer
Look at the second to last solution found at the link below. (Title: Advanced Technique) The one that uses the SUMPRODUCT function in the Conditional Formatting formula.

Eliminate the part of the CF formula that deals with the columns, unless you want that feature also.

You can probably also clean up the code if you don't need the Columns highlighted. I didn't bother as simply changing the CF formula eliminated the column highlighting.

http://www.tushar-mehta.com/publish...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
November 20, 2018 at 21:53:28
Thank you, that looks like it's solving my request!

Reply ↓  Report •

Ask Question