This code gets the cell's colour regardless of whether it's set from conditional formatting or not. It currently works on 2010 and unsure about older versions of excel.

My question is is there a way to make this code more efficient?

• Such as being able to highlight an area for the code to search rather than setting For stantments

• Getting it to search for a specific colour you set rather than red or whatever has been set

• Anything else anyone can think of

<!>

Sub myCFtest()

For q = 1 To 26

sCol = Split(ActiveCell.Address, "$")(1)
sColNum = sCol & 1
Range(sColNum).Select

For i = 1 To 100

sColNum = ActiveCell.Address

    If Range(sColNum).DisplayFormat.Interior.Color = 255 Then
        Y = ActiveCell.Address
        MsgBox ("Red Cell Found At " & Y)
    End If

ActiveCell.Offset(1, 0).Select

Next i

ActiveCell.Offset(0, 1).Select

Next q

MsgBox ("No Red Cell Found")

End Sub

Edit:

OK so I have updated the code with some of the suggestions below, just to let you know as well I am still quite new to VBA and I cant quite get my head around not using activecell and .select yet, though once I do I will use it

Updated code is below, I would like to know what other improvements could be made

Sub myCFtest2()

Dim sColumnLetter As String
Dim sColumnNumber As String
Dim rRowNumber As Long
Dim rColumnNumber As Long



For rColumnNumber = 1 To 26

sColumnLetter = Split(ActiveCell.Address, "$")(1)
sColumnNumber = sColumnLetter & 1
Range(sColumnNumber).Select

For rRowNumber = 1 To 100


FindInteriorColor = 255

If Cells(RowNumber, ColumnNumber).DisplayFormat.Interior.Color = FindInteriorColor Then
MsgBox ("Red Cell Found at " & Cells(rRowNumber, rColumnNumber).Address)
End If

ActiveCell.Offset(1, 0).Select

Next RowNumber

ActiveCell.Offset(0, 1).Select

Next ColumnNumber

End Sub