VBA to check if any cells on the row have a colour

This quick script will scan through the worksheet(“X”) and if there is a coloured cell on the current row, it will put a 1 in the first empty column so you can filter easily.

Make sure you change the name of the worksheet in the code to reflect the one you are checking.

'Speed up the VBA
Application.ScreenUpdating = False

'Change the "X" in the Worksheets command to whatever your sheet is called...

'Identifies the last used row in Column A on the worksheet you are working with
With Worksheets("X")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Identifies the last used column using the headers
With Worksheets("X")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

'Loops through all cells on the row and if coloured puts a 1 in the column after the last used column
'Also breaks out of the check on that row once colour identified as already satisfied condition
For r = 2 To lastrow
For c = 1 To lastcol

If Worksheets("X").Cells(r, c).Interior.Color <> 16777215 Then
Worksheets("X").Cells(r, lastcol + 1) = 1
Exit For
End If

Next c

Next r

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us