coloring the last used row to red?

  • Thread starter Thread starter Martyn
  • Start date Start date
M

Martyn

Hi,
Can I color the last used row of a worksheet to red automatically before I
close the workbook?
TIA
 
Hi
put the following code in your workbook module (not in a standard
module):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks as worksheet
Dim lastrow as range
set wks = me.worksheets("sheet1")
set LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).entirerow
lastrow.interior.colorindex = 3
application.displayalerts=false
me.save
application.displayalerts=true
End Sub
 
Another way if Format|conditional Formatting is available.

Select A1 and then hit ctrl-A (twice in xl2003) to select all the cells.

Format|conditional formatting

Use a formula is:
=AND(COUNTA(1:1)>0,COUNTA(2:$65536)=0)

And give it a nice color pattern.
 
Thank Frank, Your code does the job. But I am sorry to mislead everyone by
saying that I needed to color the row.
Actually what I meant is to color the "fonts" of the last used row to red
not the "interior". Could just changing the line
lastrow.interior.colorindex=3
to
lastrow.font.colorindex=3
do the job?
Thanks
 
Thanks for your help Dave,

Dave Peterson said:
Another way if Format|conditional Formatting is available.

Select A1 and then hit ctrl-A (twice in xl2003) to select all the cells.

Format|conditional formatting

Use a formula is:
=AND(COUNTA(1:1)>0,COUNTA(2:$65536)=0)

And give it a nice color pattern.
Click to expand...
 
Hi
is this a question?
If it is: Yes changing the last line according to your suggestion will
do
 
Back
Top