Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Cell When Interior.ColorIndex =3
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Cell When Interior.ColorIndex =3
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 Ron wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
more on interior.colorindex issue | Excel Programming | |||
interior.colorindex does not work? | Excel Programming | |||
problem with interior.colorindex | Excel Programming | |||
Use of Interior.ColorIndex | Excel Programming | |||
Cell.interior.colorindex - does not work in Excel 97 | Excel Programming |