VBA Excel filling up interior color for Entirerow just to the last used column

Question

I am trying to fill up my rows with colour down to the last used column.

I tried:

 For i = 2 To lRow
 If ActiveSheet.Range("O" & i).Value = "FULL" Then
 ActiveSheet.Range ("O" & i), Cells(lCol).Interior.Color = RGB(155, 255, 0)
 ElseIf ActiveSheet.Range("O" & i).Value = "NEW" Then
 ActiveSheet.Range("O" & i).EntireRow.Interior.ColorIndex = 33
 End If
 Next

But I am getting "Application defined or object-defined error".

This code is condition-based. When the value "FULL" or "NEW" is to be found down to the last row, then the row with this value has to be highlighted. The problem is, that I don't want to use the entirerow property, as this colour is seen forever. I just need to restrict the .interior.Color property to my last column used.

How can I do that?

I found some other solutions, from where I tried to solve this issue, but in vain.

How to select a range of the second row to the last row

How do I find the last column with data?

If `lCol` variable holds last column then you could use `ActiveSheet.Range("A" & i).Resize(1,lCol),Interior.Color` to color specific cells in a row. Question is what does `lCol` hold in reality? — shrivallabha.redij, Feb 11 '21 at 15:50

score 0 · Answer 1 · answered Feb 11 '21 at 17:21

First of all, we need to define the lcol properly

Dim wo as Worksheet Dim lCol as Long

Then always set our worksheet as a priority

  Set wo = ThisWorkbook.ActiveSheet

  lCol = wo.UsedRange.Columns(wo.UsedRange.Columns.Count - 14).Column

And afterward, define our last column.

I've put -14, which means that the last 14 columns won't be affected, as per the explanation here:

ActiveSheet.UsedRange.Columns.Count - 8 what does it mean?

Next my code should look like this:

For i = 2 To lRow
If ActiveSheet.Range("O" & i).Value = "FULL" Then
ActiveSheet.Range("A" & i).Resize(1, lCol).Interior.Color = RGB(155, 255, 0)
ElseIf ActiveSheet.Range("O" & i).Value = "NEW" Then
ActiveSheet.Range("O" & i).EntireRow.Interior.ColorIndex = 33
End If
Next

referring to the very first column A instead of "O", the filtered value was picked up from.

Another way you can find here:

https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

VBA Excel filling up interior color for Entirerow just to the last used column

1 Answers1