Check the Font Name in a sheet and if its not Arial the highlight the entire row

Question

For i=1 to Lastrow
If cells(i,1).Worksheets("Name").Font.Name<> "Arial" then
cells(i,1).Entirerow.interior.colorindex= 3
Else cells(i,1).Entirerow.interior.colorindex= 2
End if
Next i

This code is not working for me. I want to check if font is Arial and if it is the don't do anything else highlight the row with red color. Please guide.

How exactly it doesn't work? Does it throw an error or something? — AntiDrondert, Aug 14 '18 at 11:17
You need to change the order of `cells(i,1).Worksheets("Name")` to `Worksheets("Name").Cells(i, 1)`. Also, change `cells(i,1).Entirerow` to `Worksheets("Name").Rows(i)`. — user7393973, Aug 14 '18 at 11:18

user7393973 · Answer 1 · 2018-08-14T11:35:31.653

1

You need to change the order of cells(i,1).Worksheets("Name") to Worksheets("Name").Cells(i, 1).

Also, change cells(i,1).Entirerow to Worksheets("Name").Rows(i).

I would also advise using a With Worksheets("Name").

Result:

With Worksheets("Name")
  For i = 1 to Lastrow
    If .Cells(i, 1).Font.Name<> "Arial" then
      .Rows(i).Interior.ColorIndex= 3
    Else
      .Rows(i).Interior.ColorIndex= 2
    End if
  Next i
End With

edited Aug 14 '18 at 11:35

answered Aug 14 '18 at 11:21

user7393973

2,270
1
20
58

Nothing wrong with using EntireRow as far as I know? – SJR Aug 14 '18 at 11:24
1

@SJR Nothing. I just prefer this way. :) – user7393973 Aug 14 '18 at 11:25
You should move the `With` outside the `For` loop. – chris neilsen Aug 14 '18 at 11:34
@chrisneilsen - why, is it more efficient? – SJR Aug 14 '18 at 11:36
@chrisneilsen I guess you're right. I'm not sure if it make any difference after "compiled" so I had made its scope as small as needed, but it might be opening and closing in every iteration. Edit: ops, I mention the wrong person. – user7393973 Aug 14 '18 at 11:36
@sjr yes, it is. Since the With block object is not dependant on the For loop iterator, doing it once outside the loop will be faster – chris neilsen Aug 14 '18 at 11:43
Thanks for clarifying @chrisneilsen – SJR Aug 14 '18 at 11:47

score 1 · Answer 2 · answered Aug 14 '18 at 11:23

1

@user7393973 has identified your main problem, but you could avoid the If this way.

Sub x()

Dim i As Long

For i = 1 To Lastrow
    Worksheets("Name").Cells(i, 1).EntireRow.Interior.ColorIndex = IIf(Worksheets("Name").Cells(i, 1).Font.Name = "Arial", 2, 3)
Next i

End Sub

answered Aug 14 '18 at 11:23

SJR

22,986
6
18
26

1

That's optional to any `If...Else...End If`, but I will upvote your answer regardless. – user7393973 Aug 14 '18 at 11:24
Second thought, maybe it wouldn't be ideal if there were multiple different instructions, so yes, it isn't optional to any, so that's a good use of it. – user7393973 Aug 14 '18 at 11:30
Yes, to clarify this approach is best if you have only two choices, though you can nest Iif statements. However I would recommend sticking with If or using Select Case for more complicated scenarios. – SJR Aug 14 '18 at 11:33
This is more of a small question, but would using `Integer` be better or is there edge cases where it wouldn't work? My Excel 2013 has 1 048 576 rows per sheet. – user7393973 Aug 14 '18 at 11:44
No, it's better to use Long in (pretty much) all circumstances. In any case Integer is generally converted to Long "behind the scenes", https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – SJR Aug 14 '18 at 11:47
When I removed lastrow with a number its working for me. Please guide why lastrow was not being considered by VBA? – Samad Raza Aug 14 '18 at 16:08
How are you defining it and what is it’s value? – SJR Aug 14 '18 at 16:44

Check the Font Name in a sheet and if its not Arial the highlight the entire row

2 Answers2