Determine if Cells in Range meet Criteria 1

[Bass71]

I am trying to determine any of the cells in a range have the format <<interior.colorindex = 3>>

THis is what I have now...
Application.WorksheetFunction.CountA(Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(7, 0))) = 0 Then

However, this is not specific and so I imagine I need to replace the CountA function with some kind of test function.

thanks for the expertise.....RO

 

[Loomah]

Hi
I'm not sure what you're using CountA for as it counts cells that have content - not formatting. However I may be missing something - I often am!!

If I understand your requirement this should do the trick

Sub a()
Dim c As Range
For Each c In Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(7, 0))
    If c.Interior.ColorIndex = 3 Then
        MsgBox "Cell " & c.Address & " has colorindex 3!"
    End If
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Guest_imported]

I have a marketing spreadsheet containing a few sheets. When the first sheet is filled in, I need it to populate copy another sheet with any relevant data. For example, if I check off 2 items in column 2 on sheet 1, and 3 items of column 30, it needs to populate sheet 10 with all the info without leaving any blank columns.
Here is an example: Sheet 1 has 30 rows with different products. If I enter 3 in column 3 for the quantity of item in row 1, it should copy that data to sheet 3. It should look for data in column 3 of sheet 1 and if there is data, copy it to sheet 10 one row at a time until there is no more data, and then exit. Any ideas?

 

[Loomah]

deadwood
Would you start a fresh thread for this please?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​