Conditional Formatting - Last Record

Duvnjak

New Member
Joined
Aug 14, 2003
Messages
27
Hello,

I am wondering if there is an easy way to conditionally format the last record in my spreadsheet. Seems simple enough but there is a catch: my dilemna is that the location or row of the last record might change (i.e. row 35 one day and row 39 the next). I created a PivotTable using Excel 2007 and then converted this to be formula based. The number of records produced by this report might grow over the course of the year and I would like to be able to highlight or bold the last row showing the grand total numbers.

Thanks to all in advance.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Assuming you know that your last row in column A will be numeric then I would do the following:

1. Create a defined name wcalled LastRw with a definition of:

=MATCH(9.9E+307,$A:$A)

2. Select a sufficiently large range in your worksheet eg A1:Z2000 and go Format>Conditional Formatting.
3. Change to Formula Is and use:

=ROW()=LastRw

and then hit the Format button and format as required.

If column A contains a text value in the last row (eg "Grand Total" then replace the 9.9E+307 with "zzzzzzz"
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you data is contiguous starting in row 1 you can also use a conditional formatting formula like:

=ROW(A1)=ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))
 

Duvnjak

New Member
Joined
Aug 14, 2003
Messages
27
Thank you both for your responses.

I'm still having some difficulty getting this to work properly in Excel 2007. The data in my Column A is all text, with one row in between being null.

Something I forgot to mention - the last row in my column will always show up as "Grand Total". I tried using this formula:

=MATCH("Grand Total",Sheet1!$A:$A)

It provides me with a result of 54 (the correct value). However, I also get an error message that shows up as a tool tip saying:

"The formula in this cell refers to cells that are currently empty."

When I go to trace the empty cell it points to the top cell (A1).

Any ideas?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If it is the only instance of "Grand Total" in that column then you could use:

=MATCH("Grand Total",Sheet1!$A:$A,0)
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
Richard,
..been meaning to ask, but is this 9.9E+307 a constant
that can be used to bring back the highest possible number imaginable
by Excel?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Jim - it's pretty darned close to the largest number which Excel can handle! It's a standard number written in scientific notation (so the E+307 represents "times 10 to the power of 307") - sufficiently large for everything I can think of!
 

brinraeven

Board Regular
Joined
Nov 22, 2007
Messages
51
I none of the cells are blank, you could use this under your conditional formatting:

=ROW($A1)=COUNT($A$1:$A$10)

If you know that a certain number of cells (let's use 1) is always going to be blank, you could use:

=ROW($A1)=COUNT($A$1:$A$10)+1

If the column is text only or numbers and text, use:

=ROW($A1)=COUNTA($A$1:$A$10)

Let me know if that helps.
 

Duvnjak

New Member
Joined
Aug 14, 2003
Messages
27
Thank you all. The following worked like a charm!

For the named range LastRow (column A):

=MATCH("Grand Total",Sheet1!$A:$A,0)

Condition:

=ROW()=LastRow
 
Last edited:

Forum statistics

Threads
1,081,727
Messages
5,360,910
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top