identify consecutive columns they are coloured
 

Hi,

Does anyone know whether there is a way to identify within a range of
F1:CU:5000, which columns is coloured consecutively? (ie. I want to
see which row is coloured in Red from F to CU Throughout.)

Thanks.
Elaine.

identify consecutive columns they are coloured
 

Sub FindRed()
Dim rng as Range, rng1 as Range
Dim cell as Range
set rng = Range("F1:CU5000")
for each cell in rng
if cell.interior.colorIndex = 3 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.select
end if
End Sub

--
Regards,
Tom Ogilvy

"elaine" wrote:

Hi,

Does anyone know whether there is a way to identify within a range of
F1:CU:5000, which columns is coloured consecutively? (ie. I want to
see which row is coloured in Red from F to CU Throughout.)

Thanks.
Elaine.

identify consecutive columns they are coloured
 

Elaine,
Try this:

Private Sub CommandButton2_Click()
Dim CompleteRows As Range

Const INDEX_RED As Long = 3

Set CompleteRows = GetCompleteColouredRows(Range("C6:G8"), INDEX_RED)

If Not CompleteRows Is Nothing Then
MsgBox CompleteRows.Address
Else
MsgBox "No complete rows coloured"
End If

End Sub

Private Function GetCompleteColouredRows(CheckRange As Range,
ColourIndexToMatch As Long) As Range
Dim i As Long
Dim MatchedRange As Range

With CheckRange
For i = 1 To .Rows.Count
If .Rows(i).Interior.ColorIndex = ColourIndexToMatch Then
If MatchedRange Is Nothing Then
Set MatchedRange = .Cells(i, 1)
Else
Set MatchedRange = Union(MatchedRange, .Cells(i, 1))
End If
End If
Next
End With

Set GetCompleteColouredRows = MatchedRange

End Function

NickHK

"elaine" wrote in message
oups.com...
Hi,

Does anyone know whether there is a way to identify within a range of
F1:CU:5000, which columns is coloured consecutively? (ie. I want to
see which row is coloured in Red from F to CU Throughout.)

Thanks.
Elaine.