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
Select First Cell Depending on Interior Color
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 79561
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Select First Cell Depending on Interior Color
Where do you want to search? Through the entire sheet, or in a specific row, or in a specific column?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Select First Cell Depending on Interior Color
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
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
-
- Administrator
- Posts: 79561
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Select First Cell Depending on Interior Color
Try this code:
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.
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 Test macro uses FindColor to look for yellow in columns L and AA, and selects the cell if found.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Select First Cell Depending on Interior Color
Thanks Hans
As usual, your code hit the mark!
Kind Regards'
Mohamed
As usual, your code hit the mark!
Kind Regards'
Mohamed