Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fixing code highlight lastrow

0

hello

i  try to  highlight  the  lastrow   after  sum  data    it  dosn't works perfectly  and i  have  a problem  it  also highlight  after   col b  every time  run  the  macro    it  supposes   highlighting   the  lastrow   a:b   and   if   i  run  the  macro repeatedly   it should make  the  rows which before  last row contain no  color   just  the  lastrow  every time  run the  macro 

Sub highlight()
Dim r, ir As Long
ir = Cells(Rows.Count, 2).End(xlUp).Row
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveCell.Offset(0, 0).Value = "total"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=sum(b2:b" & ir & ")"
For c = 1 To ir
r = ActiveSheet.Cells(ActiveSheet.Rows.Count, c).End(xlUp).Row
Cells(r, c).Interior.ColorIndex = 4
Next
End Sub
excel
MS Office Version: 2010
posted by: leopard (rep: 88) on Tue Aug 11, 2020 at 4:47 am
Answer
Discuss

Answers

0
Selected Answer

This is the code you probably want. Please try it.

Sub Highlight()
    ' 076

    Dim R As Long, Rl As Long
    Dim Rng As Range

    Rl = Cells(Rows.Count, 2).End(xlUp).Row
    If Cells(Rl, "A").Value = "total" Then
        Rows(Rl).Delete
        Rl = Rl - 1
    End If

    Set Rng = Range("B2:B" & Rl)
    Rng.Interior.ColorIndex = 0                 ' remove previous

    Rl = Rl + 1
    Cells(Rl, "A").Value = "total"
    Cells(Rl, "B").Value = Application.Sum(Rng)
    Range(Cells(Rl, "A"), Cells(Rl, "B")).Interior.ColorIndex = 4
End Sub

And this is what you should learn about writing code:-

  1. In Dim R, Rl As Long R is a Variant and only Rl is a Long.
  2. Never Select anything except to show to the user. VBA doesn't need it. Just address the range (or cell) by its name or address and do with it whatever you want.
posted by: Variatus (rep: 4889) on Tue Aug 11, 2020 at 8:25 pm
Discuss

Discussion

thanks  Variatus the  code  works   efficiently  you've solvrd  my  problem
leopard (rep: 88) Aug 12, '20 at 12:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login