Find Cell When Interior.ColorIndex =3

[Ron]

Hello all,
Due to change in scope of my project I'm trying edit this code to meet
the requirements. Find a cell when the interior ColorIndex = 3
(Red). The cell could possibly be blank or it could hold data. If
the code finds a cell with the interior formatted to = 3 (Red) then
"MsgBox "Please correct any cells highlighted RED and click on the
Validate Button." If the code does not find any cells with interior
formating equaling 3 (Red) then MsgBox("Data validated, good job!" If
the sheet is to be printed, clicking on the Print Setup button
prepares the file for printing.".

My first attempt was looking for a cell with Red font but, realized
that I had some blank cells that I need to flag so, the Red font
approach did not work well with blank or empty cells.
Thank you all for your assistance,

Sub FindRedFont()
Dim UserResponse As Variant
On Error GoTo NoRedFonts
Application.FindFormat.Interior.ColorIndex = 3
Range("I12:AI10000").Find("*", After:=Range("AI10000"), _
SearchFormat:=True, SearchOrder:=xlByColumns).Select

MsgBox "Please correct any cells highlighted RED and click on the
Validate Button" & vbNewLine & "" & vbNewLine & _
"", , "Jrnl 1 Corrections"
Exit Sub

NoRedFonts:
UserResponse = MsgBox("Data validated, good job!" _
& vbNewLine & vbNewLine & _
"If the sheet is to be printed, " & _
"clicking on the Print Setup button " & _
"prepares the file for printing.", _
vbExclamation + vbOKCancel, "TEST")
If UserResponse = vbCancel Then
Exit Sub 'Or other required code
End If

End Sub

 

[Dave Peterson]

Option Explicit
Sub FindRedFont()
Dim FoundCell As Range

'just in case there's other stuff that's been specified
Application.FindFormat.Clear
Application.FindFormat.Interior.ColorIndex = 3

With ActiveSheet
Set FoundCell = .Range("I12:ai10000").Find(What:="", _
After:=.Range("Ai10000"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)

If FoundCell Is Nothing Then
MsgBox prompt:="Data validated, good job!" _
& vbNewLine & vbNewLine & _
"If the sheet is to be printed, " & _
"clicking on the Print Setup button " & _
"prepares the file for printing.", _
Buttons:=vbExclamation + vbOKCancel, _
Title:="TEST"
Else
Application.Goto FoundCell, scroll:=True
MsgBox prompt:="Please correct any cells highlighted RED " _
& "and click on the Validate Button" _
& vbNewLine & "Start with: " _
& FoundCell.Address(0, 0), _
Title:="Jrnl 1 Corrections"

End If
End With
End Sub