Determine if Range contains distinct Value 2

[Bass71]

Hello,

I'm trying to find the function that will determine if any cells in a range of 7 rows by 1 column contain ColorIndex 38.
Instead of writing;

If ActiveCell(2, 1).Selection.ColorIndex = 38 Or ActiveCell(3, 1).Selection.ColorIndex = 38 _
Or ActiveCell(4, 1).Selection.ColorIndex = 38 Or ActiveCell(5, 1).Selection.ColorIndex = 38 _
Or ActiveCell(6, 1).Selection.ColorIndex = 38 Or ActiveCell(7, 1).Selection.ColorIndex = 38 _
Or ActiveCell(8, 1).Selection.ColorIndex = 38 Then

Thank you ............RO

 

[byundt]

Assuming that the color is not determine by Conditional Formatting, then you might test the highlighting color using:

Dim i As Long
Dim boo As Boolean
For i=1 To 7
     If ActiveCell.Offset(i,0).Interior.ColorIndex=38 Then boo=True
Next
If boo Then   'Your code starts here

 

[xlbo]

another way to skin the poor ol' moggy

dim c as range, testRange as range

set testRange = Range(offset(Activecell,,7,1))
for each c in testRange
  if c.colorindex = 38 then
     'do stuff here
  else
     'do other stuff here
  end if
next

 

[rmikesmith]

another way to skin the poor ol' moggy

I can always count on you as a multi-discipline resource: whether adding to my VBA knowledge or improving my vocabulary


Regards,
Mike

 

[xlbo]

LOL - cheers Mike - not sure improving is the word tho....