Highlights only last rows

motilulla · Oct 29, 2017

Hello,

I am looking if someone can make VBA that can highlights last rows in the column C:H as per data last data row column K:Q as per example below

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

3

4

N

Year

n1

n2

n3

n4

n5

n6

n7

EM1

N1

N2

N3

N4

N5

N6

N7

5

N

Year

n1

n2

n3

n4

n5

n6

n7

EM1

N1

N2

N3

N4

N5

N6

N7

6

1

70/71

X

1

2

1

2

1

3

2

5

4

1

7

2

70/71

2

1

X

2

5

1

4

2

1

4

8

3

70/71

2

1

2

1

2

1

3

5

2

1

9

4

70/71

1

X

1

X

5

2

1

4

5

10

5

70/71

2

1

2

X

1

X

2

3

6

1

11

6

70/71

2

1

X

1

6

1

2

1

12

7

70/71

1

X

2

3

2

6

2

5

4

13

8

70/71

1

X

1

2

1

2

1

4

1

2

1

2

1

14

9

70/71

1

2

1

X

1

2

3

1

3

10

8

15

10

70/71

2

1

X

1

2

4

2

3

1

3

2

16

11

70/71

2

X

2

1

2

1

3

7

1

3

1

17

12

70/71

2

1

X

2

1

3

1

11

1

2

1

18

13

70/71

1

2

X

2

1

2

4

3

1

2

1

2

1

19

14

70/71

X

1

2

1

13

3

4

5

1

5

20

15

70/71

1

2

1

2

3

6

3

5

1

2

21

16

70/71

X

2

1

2

1

2

3

2

4

2

22

17

70/71

1

2

X

1

X

2

4

2

11

23

18

70/71

1

2

X

1

2

1

2

5

2

1

2

3

24

19

70/71

2

1

2

1

2

1

7

2

1

3

2

3

25

20

70/71

1

2

X

1

2

1

3

15

1

26

21

70/71

2

1

X

2

1

2

1

5

1

2

1

3

27

22

70/71

1

X

2

1

2

1

2

8

2

3

1

2

28

23

70/71

1

2

X

2

1

5

2

1

5

2

29

24

70/71

X

1

X

2

1

2

8

3

1

9

6

2

1

30

25

70/71

X

1

2

1

4

3

1

2

3

31

26

70/71

1

2

X

3

4

2

3

1

9

32

33

Sheet10

Thank you all
Excel 2000
Regards,
Moti

Scott T · Oct 29, 2017

Try this. I assume that C6:I last row should not have any color except that witch the macro applies. So all color is removed before the new colors are applied.

Code:

Sub hllr()

Dim lr As Long


lr = Cells(Rows.Count, 1).End(xlUp).Row
 
Range("C6:H" & lr).Interior.Color = xlNone
 
Range(Cells(lr - Cells(lr, 11) + 1, 3), Cells(lr, 3)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 12) + 1, 4), Cells(lr, 4)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 13) + 1, 5), Cells(lr, 5)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 14) + 1, 6), Cells(lr, 6)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 15) + 1, 7), Cells(lr, 7)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 16) + 1, 8), Cells(lr, 8)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 17) + 1, 9), Cells(lr, 9)).Interior.ColorIndex = 8


End Sub

MARK858 · Oct 29, 2017

Not sure what you mean by "as per data last data row column K:Q" or why your other cells are already filled so as a starter the below highlights the last row from columns C to H, then you can expand on what you actually want.

Code:

Sub lstrwH()
Intersect(Rows(Cells(Rows.Count, "A").End(xlUp).Row), Columns("C:H")).Interior.ColorIndex = 6
End Sub

EDIT: Please ignore the above post as I think having seen Scott T's post he has grasped the question which I hadn't.

motilulla · Oct 29, 2017

Scott T said:

Try this. I assume that C6:I last row should not have any color except that witch the macro applies. So all color is removed before the new colors are applied.

Code:

Sub hllr()

Dim lr As Long


lr = Cells(Rows.Count, 1).End(xlUp).Row
 
Range("C6:H" & lr).Interior.Color = xlNone
 
Range(Cells(lr - Cells(lr, 11) + 1, 3), Cells(lr, 3)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 12) + 1, 4), Cells(lr, 4)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 13) + 1, 5), Cells(lr, 5)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 14) + 1, 6), Cells(lr, 6)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 15) + 1, 7), Cells(lr, 7)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 16) + 1, 8), Cells(lr, 8)).Interior.ColorIndex = 8
Range(Cells(lr - Cells(lr, 17) + 1, 9), Cells(lr, 9)).Interior.ColorIndex = 8


End Sub

Thank you Scott T, worked perfect! Even better than as accepted

Have a good weekend

Regards,
Moti

motilulla · Nov 1, 2017

Hello,

Please can you help?

I require a modification instead of highlighting the only the last rows, highlight number 6 as per assigned in column "S" and then highlight the L:Q row And then highlights the column cells C:I as per numbers are shown in the row L:Q

Example.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

3

4

N

Year

n1

n2

n3

n4

n5

n6

n7

EM1

N1

N2

N3

N4

N5

N6

N7

EM2

5

N

Year

n1

n2

n3

n4

n5

n6

n7

EM1

N1

N2

N3

N4

N5

N6

N7

EM2

6

1

70/71

X

1

2

1

2

1

3

2

5

4

1

5

7

2

70/71

2

1

X

2

5

1

4

2

1

4

5

8

3

70/71

2

1

2

1

2

1

3

5

2

1

5

9

4

70/71

1

X

1

X

5

2

1

4

5

10

5

70/71

2

1

2

X

1

X

2

3

6

7

1

7

11

6

70/71

2

1

X

1

6

1

2

1

6

12

7

70/71

1

X

2

3

2

6

2

5

9

4

9

13

8

70/71

1

X

1

2

1

2

1

4

1

2

1

2

1

4

14

9

70/71

1

2

1

X

1

2

3

1

3

10

8

10

15

10

70/71

2

1

X

1

2

4

2

3

1

3

2

4

16

11

70/71

2

X

2

1

2

1

3

7

1

3

1

7

17

12

70/71

2

1

X

2

1

3

1

11

1

2

1

11

18

13

70/71

1

2

X

2

1

2

4

3

1

2

1

2

1

4

19

14

70/71

X

1

2

1

13

3

4

5

1

5

13

20

15

70/71

1

2

1

2

3

6

3

5

1

2

6

21

16

70/71

X

2

1

2

1

2

3

2

4

2

4

22

17

70/71

1

2

X

1

X

2

4

2

11

23

18

70/71

1

2

X

1

2

1

2

5

2

1

2

3

5

24

19

70/71

2

1

2

1

2

1

7

2

1

3

2

3

7

25

20

70/71

1

2

X

1

2

1

3

15

1

15

26

21

70/71

2

1

X

2

1

2

1

5

1

2

1

3

5

27

22

70/71

1

X

2

1

2

1

2

6

2

3

1

2

6

28

23

70/71

1

2

X

2

1

5

2

1

5

2

5

29

24

70/71

X

1

X

2

1

2

8

3

1

9

6

2

1

9

30

25

70/71

X

1

2

1

4

3

1

2

3

4

31

26

70/71

1

2

X

3

4

2

3

1

9

32

Sheet10

Thank you all
Excel 2000
Regards,
Moti

Scott T · Nov 1, 2017

So the contents of the cell no longer matters? Just of column S is 6? What should happen if the number of rows to highlight is greater then the number of rows that is on row 11 if the number of rows to highlight is 9 but there are only 6 rows to highlight.

MickG · Nov 1, 2017

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG01Nov56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("B6", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 17).Value = 6 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] ac = 1 To 7
        [COLOR="Navy"]Set[/COLOR] R = Dn.Offset(, ac + 8)
        R.Interior.ColorIndex = 8
        Dn.Offset(-R + 1, ac).Resize(R).Interior.ColorIndex = 8
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

motilulla · Nov 1, 2017

Scott T said:
So the contents of the cell no longer matters? Just of column S is 6? What should happen if the number of rows to highlight is greater then the number of rows that is on row 11 if the number of rows to highlight is 9 but there are only 6 rows to highlight.

I didn't thought that yes I think it only can affect if 6 is find in the cells S10, S9, S8, S7 or in the S6 (because 6 is the max number of Row11 (K11:Q11)) in this case we can limit start row 11 to down may be.

motilulla · Nov 1, 2017

MickG said:

Try this:-

Code:

[COLOR=navy]Sub[/COLOR] MG01Nov56
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range("B6", Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Dn.Offset(, 17).Value = 6 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] ac = 1 To 7
        [COLOR=navy]Set[/COLOR] R = Dn.Offset(, ac + 8)
        R.Interior.ColorIndex = 8
        Dn.Offset(-R + 1, ac).Resize(R).Interior.ColorIndex = 8
    [COLOR=navy]Next[/COLOR] ac
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you MickG, it is perfect solution as Scott T question not to overlap if 6 find before the B11 I just changed B6 to B11

What if I ask can you make it could be highlighted in 2 alternate colours like cyan and green (I mean to say row 11 in cyan row 20 in green row 22 in cyan may be)

Regards,
Moti

Scott T · Nov 1, 2017

This should only highlight rows 6 and down

Code:

Sub highlight()
Dim lr As Long
Dim cell As Range
Dim myrow As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("C6:I" & lr).Interior.Color = xlNone
For Each cell In Range("S6:S" & lr)
    If cell = 6 Then
        myrow = cell.Row
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 11) + 1, 3), Cells(myrow, 3))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 11) + 1, 3), Cells(myrow, 3)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 3), Cells(myrow, 3)).Interior.ColorIndex = 8
            End If
            
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 12) + 1, 4), Cells(myrow, 4))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 12) + 1, 4), Cells(myrow, 4)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 4), Cells(myrow, 4)).Interior.ColorIndex = 8
            End If
            
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 13) + 1, 5), Cells(myrow, 5))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 13) + 1, 5), Cells(myrow, 5)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 5), Cells(myrow, 5)).Interior.ColorIndex = 8
            End If
            
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 14) + 1, 6), Cells(myrow, 6))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 14) + 1, 6), Cells(myrow, 6)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 6), Cells(myrow, 6)).Interior.ColorIndex = 8
            End If
            
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 15) + 1, 7), Cells(myrow, 7))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 15) + 1, 7), Cells(myrow, 7)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 7), Cells(myrow, 7)).Interior.ColorIndex = 8
            End If
        
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 16) + 1, 8), Cells(myrow, 8))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 16) + 1, 8), Cells(myrow, 8)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 8), Cells(myrow, 8)).Interior.ColorIndex = 8
            End If
            
            If Intersect(Range("C5:I5"), Range(Cells(myrow - Cells(myrow, 17) + 1, 9), Cells(myrow, 9))) Is Nothing Then
                Range(Cells(myrow - Cells(myrow, 17) + 1, 9), Cells(myrow, 9)).Interior.ColorIndex = 8
            Else
                Range(Cells(6, 9), Cells(myrow, 9)).Interior.ColorIndex = 8
            End If
            
    End If

Next cell
End Sub

Highlights only last rows

motilulla

Well-known Member

Excel Facts

Scott T

Well-known Member

MARK858

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Scott T

Well-known Member

MickG

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

Scott T

Well-known Member

Similar threads

Forum statistics

Share this page

Highlights only last rows

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock