PDA

View Full Version : [SOLVED:] Highlight the first row in every data block



mpeterson
04-20-2019, 06:51 AM
Hi all,

I have a large data sheet made of 8 columns (A:H) and over 45000 rows.
Data are sorted into sets of variable number of rows. Each set of data is separated from the following with one blank row.

I am in need to only highlight all the FIRST ROWS of those data sets. In other words, I need to highlight the first row after every empty row.

The follwoing code highlights only the last row in the data sets, but what I need is the reverse.


Sub HighlightLastRow()Range("A1:S" & Range("A" & Rows.Count).End(xlUp).Row).FormatConditions.Add(xlExpression, xlEqual, "=OFFSET($A1,1,0)=""""").Interior.Color = vbYellow End Sub

May I get some help with this question please?

All the best

M.

Fluff
04-20-2019, 07:10 AM
Change the 1 in the offset to -1

mpeterson
04-20-2019, 07:18 AM
Thank you very much Fluff for your input, yes your advice helped me resolve my problem.

Thank you very much.

M.

Fluff
04-20-2019, 07:25 AM
You're welcome & thanks for the feedback

rothstein
04-22-2019, 11:29 AM
I know this thread has been marked SOLVED, but I thought the OP might be interested in this direct solution which does not involve loading up cells with Conditional Formatting.



Sub HighlightLastRow()
Intersect([A:H], [A:A].SpecialCells(xlBlanks).Offset(1).EntireRow).Interior.Color = vbYellow
End Sub



The above code does not color the first block's first row (neither did the code you posted once modified as per Fluff's comment). If you need that, you will need to add another line of code to handle it.