Hi All,

I am trying working on a spreadsheet that has users enter data into cells that are color coded. For example, if a cell has a yellow background, it needs to be populated. If it's not populated, the background color needs to be changed to a red and a msgbox needs to pop up and tell them "hey, you didn't fill out all required fields!"

I feel like I have the code figured out to define the range of the cells that need to be validated (with the help of taking examples I've found online and tweaking them). I also feel like I've figured out how to change the background colors of empty yellow cells to red. I'm just stuck on how to have the range searched for any red background cells and create a msgbox ONCE to let the user know there is a field or more than one field that needs to be filled out). A few weeks ago, I had figured out a way to have it display a message box for EVERY cell, but that is totally cumbersome if there are 100 cells with a red background. Here's the code I'm working with so far:

Sub FindUsedRange()
    Dim LastRow As Long
    Dim FirstRow As Long
    Dim LastCol As Integer
    Dim FirstCol As Integer
    Dim rng As Range
    Dim rngFound As Range
    
    ' Find the FIRST real row
    FirstRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row
      
    ' Find the FIRST real column
    FirstCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Column
    
    ' Find the LAST real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

    ' Find the LAST real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

ActiveSheet.Unprotect "password" 'unlock sheet

Set rng = ActiveSheet.Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol))
For Each Cell In rng
    Select Case Cell.Interior.ColorIndex
       Case Is = 6 And Cell.value = Empty
            Cell.Interior.ColorIndex = 3
       Case Is = 6 And Cell.Text <> ""
            Cell.Interior.ColorIndex = 0
       Case Is = 3 And Cell.Text <> ""
            Cell.Interior.ColorIndex = 0
    End Select
Exit For
Next Cell
    
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True _
        , AllowDeletingRows:=True, Password:="password" 'protect the worksheet and allow column formatting
End Sub