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
ForstantmentsGetting 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
