Hi all,

I'm trying to find the first cell with a font ColorIndex that equals 3
(Red). Then a MsgBox with a comment. This is where I'm at and I
don't think my code is finding the cell with red fonts.

Sub testfollowup()

Dim c As Range

For Each c In ActiveSheet.Range("K12:AI10000")
If ColorIndex = 3 Then

MsgBox "Please make additional corrections"

End If

Next c

End Sub

Thank you all for any assistance,

hi
try this......
Sub testfollowup()
Dim c As Range
For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
MsgBox "Please make additional corrections"
End If
Next c
End Sub

regards
FSt1

"Ron" wrote:

Hi all,

I'm trying to find the first cell with a font ColorIndex that equals 3
(Red). Then a MsgBox with a comment. This is where I'm at and I
don't think my code is finding the cell with red fonts.

Sub testfollowup()

Dim c As Range

For Each c In ActiveSheet.Range("K12:AI10000")
If ColorIndex = 3 Then

MsgBox "Please make additional corrections"

End If

Next c

End Sub

Thank you all for any assistance,

How did the font become red... by using conditional formatting or by
directly setting it?

--
Rick (MVP - Excel)


"Ron" wrote in message
...
Hi all,

I'm trying to find the first cell with a font ColorIndex that equals 3
(Red). Then a MsgBox with a comment. This is where I'm at and I
don't think my code is finding the cell with red fonts.

Sub testfollowup()

Dim c As Range

For Each c In ActiveSheet.Range("K12:AI10000")
If ColorIndex = 3 Then

MsgBox "Please make additional corrections"

End If

Next c

End Sub

Thank you all for any assistance,

On Jul 2, 2:32*pm, "Rick Rothstein"
wrote:
How did the font become red... by using conditional formatting or by
directly setting it?

--
Rick (MVP - Excel)

"Ron" wrote in message

...



Hi all,

I'm trying to find the first cell with a font ColorIndex that equals 3
(Red). *Then a MsgBox with a comment. *This is where I'm at and I
don't think my code is finding the cell with red fonts.

Sub testfollowup()

Dim c As Range

For Each c In ActiveSheet.Range("K12:AI10000")
* *If ColorIndex = 3 Then

* * * *MsgBox "Please make additional corrections"

* *End If

Next c

End Sub

Thank you all for any assistance,- Hide quoted text -

- Show quoted text -

Hello, font was set to red to flag an error. The code provided by
Fst1 works however, if I have more than one occurance of the red font
clicking OK or Cancel does not dismiss the msgbox and I have to kill
Excel to get out of the message box. Any suggestions? Greatly
appreciated. Thanks, Ron

hi
add this if you don't want the second occurance.
Sub testfollowup()
Dim c As Range
For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
MsgBox "Please make additional corrections"
exit sub'*******************
End If
Next c
End Sub

regards
FSt1

"Ron" wrote:

On Jul 2, 2:32 pm, "Rick Rothstein"
wrote:
How did the font become red... by using conditional formatting or by
directly setting it?

--
Rick (MVP - Excel)

"Ron" wrote in message

...



Hi all,

I'm trying to find the first cell with a font ColorIndex that equals 3
(Red). Then a MsgBox with a comment. This is where I'm at and I
don't think my code is finding the cell with red fonts.

Sub testfollowup()

Dim c As Range

For Each c In ActiveSheet.Range("K12:AI10000")
If ColorIndex = 3 Then

MsgBox "Please make additional corrections"

End If

Next c

End Sub

Thank you all for any assistance,- Hide quoted text -

- Show quoted text -

Hello, font was set to red to flag an error. The code provided by
Fst1 works however, if I have more than one occurance of the red font
clicking OK or Cancel does not dismiss the msgbox and I have to kill
Excel to get out of the message box. Any suggestions? Greatly
appreciated. Thanks, Ron

You don't have to loop to do what you want; just run this macro... it will
select the first cell with an all red font and will then popup the
MessageBox (only one time per running of the macro):

Sub FindRedFont()
Application.FindFormat.Font.ColorIndex = 3
Cells.Find("*", After:=Range("AI10000"), SearchFormat:=True).Select
MsgBox "Please make additional corrections"
End Sub

--
Rick (MVP - Excel)

Sorry, I forgot to restrict it to your K12:AI10000 range. Here is the
corrected code to do that...

Sub FindRedFont()
Application.FindFormat.Font.ColorIndex = 3
Range("K12:AI10000").Find("*", After:=Range("AI10000"), _
SearchFormat:=True).Select
MsgBox "Please make additional corrections"
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You don't have to loop to do what you want; just run this macro... it will
select the first cell with an all red font and will then popup the
MessageBox (only one time per running of the macro):

Sub FindRedFont()
Application.FindFormat.Font.ColorIndex = 3
Cells.Find("*", After:=Range("AI10000"), SearchFormat:=True).Select
MsgBox "Please make additional corrections"
End Sub

--
Rick (MVP - Excel)

On Jul 2, 8:32*pm, "Rick Rothstein"
wrote:
Sorry, I forgot to restrict it to your K12:AI10000 range. Here is the
corrected code to do that...

Sub FindRedFont()
* Application.FindFormat.Font.ColorIndex = 3
* Range("K12:AI10000").Find("*", After:=Range("AI10000"), _
* * * * * * * * * * * * * * SearchFormat:=True).Select
* MsgBox "Please make additional corrections"
End Sub

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message

...



You don't have to loop to do what you want; just run this macro... it will
select the first cell with an all red font and will then popup the
MessageBox (only one time per running of the macro):

Sub FindRedFont()
*Application.FindFormat.Font.ColorIndex = 3
*Cells.Find("*", After:=Range("AI10000"), SearchFormat:=True).Select
*MsgBox "Please make additional corrections"
End Sub

--
Rick (MVP - Excel)- Hide quoted text -

- Show quoted text -
Hi Fst1. Love you code however it only picks up a red cell if it's in
the first cell i.e. K12. Rick I get an error with your code if there
are no red cells. My scope has changed to include a msgbox should
there be no red cells in my range. I can't seem to get the first
option of finding a red cell and then a msgbox "Please make additional
corrections" to work. The code included only produces the second
msgbox. All assistance greatly appreciated.

Sub testfollowup()
Dim c As Range
Dim userResponse As Variant

For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
MsgBox "Please make additional corrections"

Select Case userResponse
Case vbCancel
Exit Sub
Case vbOK
Exit Sub
End Select

Else 'if no RED Cells are Found
userResponse = MsgBox("Data validated, good job!" _
& vbNewLine & _
"If the sheet is to be printed, " & _
"clicking on the Print Setup button " & _
"prepares the file for printing.", _
vbExclamation + vbOKCancel, "TEST")
Select Case userResponse
Case vbCancel
Exit Sub 'Or other required code
Case vbOK
Exit Sub
End Select
End If
Next c

End Sub