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 •

#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 •
Related Solutions


Ask Question