VBA To Find The Last Cell With A Specific Fill Color

Look for Last Cell Of Certain Color VBA Excel Spreadsheet Code


How can a find the last cell on a spreadsheet based on a given color?


This VBA macro code allows you to select a cell with a specific fill color and it will automatically determine the last cell within your spreadsheet containing the designated fill color. The code uses an Inverse For Loop to cycle through each cell within the UsedRange of the spreadsheet in reverse order. There are three options provided to point your user to the resulting cell.

Sub FindLastColoredCell()
'PURPOSE: Determine Last Cell On Sheet Containing Specific Fill Color
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim TargetColor As Long
Dim cell As Range
Dim sht As Worksheet
Dim x As Long

'Choose target color based on currently selected cell
  TargetColor = ActiveCell.Interior.Color

'Inversely Loop Through Each Cell In Used Range on ActiveSheet
  For x = ActiveSheet.UsedRange.Cells.Count To 1 Step -1
    If ActiveSheet.UsedRange.Cells(x).Interior.Color = TargetColor Then
      'Notify User of Cell Address [Option 1]
        MsgBox "Last Color Found: " & ActiveSheet.UsedRange.Cells(x).Address
      'Select the last cell [Option 2]
      'Scroll To The Last Cell [Option 3]
        ActiveWindow.ScrollColumn = ActiveSheet.UsedRange.Cells(x).Column
        ActiveWindow.ScrollRow = ActiveSheet.UsedRange.Cells(x).Row
      'Exit Loop
        Exit Sub
    End If
  Next x

End Sub

Alternate (Better) Solution

Excel MVP Rick Rothstein submitted this alternative way to find the last colored cell. I like this solution as it is much shorter and I am assuming faster. 

Find Format.png

Rick's macro essentially automates using the Find and Replace dialog functionality to search based on the active cell's fill color format. Here's the code:

Sub FindLastColoredCell()
'PURPOSE: Determine Last Cell On Sheet Containing Specific Fill Color
'AUTHOR: Rick Rothstein
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

'Ensure Find Formatting Rule is Reset

'Store active cell's fill color into "Find"
  Application.FindFormat.Interior.Color = ActiveCell.Interior.Color

'Notify User of location using the "Find" Action
  MsgBox "Last Color Found: " & ActiveSheet.UsedRange.Find("", , , , , xlPrevious, , , True).Address

'Reset Find Formatting Rule

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

~ Chris