Select First Cell Depending on Interior Color

[MSingh]

Hi,

I use ColorIndex 6 to set the interior color of all duplicated numbers in Sheets("Data").

How can i select the first cell with Interior.ColorIndex=6 ?

Thanks again
Mohamed

[HansV]

Where do you want to search? Through the entire sheet, or in a specific row, or in a specific column?

[MSingh]

Hi Hans,

Search in Col L in Table1.

[Would appreciate it if we could also search in Range AA3 to LastRow with Data in Col AA]. Col AA is not in Table1.

Thanks
Mohamed

[HansV]

Try this code:

Code: Select all

Sub Test()
  Dim rng As Range
  ' Try to find in column L=12
  Set rng = FindColor(ActiveSheet, 12, 6)
  If rng Is Nothing Then
    ' Not found, try to find in column AA=27
    Set rng = FindColor(ActiveSheet, 27, 6, 3)
  End If
  If rng Is Nothing Then
    Beep
  Else
    rng.Select
  End If
End Sub

Function FindColor(SearchSheet As Worksheet, ColumnNumber As Long, SearchColor As Long, Optional StartRow As Long = 1) As Range
  Dim r As Long
  Dim EndRow As Long
  With SearchSheet
    EndRow = .Cells(.Rows.Count, ColumnNumber).End(xlUp).Row
    For r = StartRow To EndRow
      If .Cells(r, ColumnNumber).Interior.ColorIndex = SearchColor Then
        Set FindColor = .Cells(r, ColumnNumber)
        Exit Function
      End If
    Next r
  End With
  Set FindColor = Nothing
End Function

The function FindColor is generic, it returns a Range object that is either the first cell found, or Nothing.
The Test macro uses FindColor to look for yellow in columns L and AA, and selects the cell if found.

[MSingh]

Thanks Hans
As usual, your code hit the mark!
Kind Regards'
Mohamed