Select First Cell Depending on Interior Color

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Select First Cell Depending on Interior Color

Post by 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

User avatar
HansV
Administrator
Posts: 79561
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select First Cell Depending on Interior Color

Post by HansV »

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

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Select First Cell Depending on Interior Color

Post by 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

User avatar
HansV
Administrator
Posts: 79561
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Select First Cell Depending on Interior Color

Post by 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.
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Select First Cell Depending on Interior Color

Post by MSingh »

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