Consulting

Results 1 to 5 of 5

Thread: Highlight the first row in every data block

  1. #1

    Highlight the first row in every data block

    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.

  2. #2
    Change the 1 in the offset to -1

  3. #3
    Thank you very much Fluff for your input, yes your advice helped me resolve my problem.

    Thank you very much.

    M.

  4. #4
    You're welcome & thanks for the feedback

  5. #5
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •