VBA to prevent saving if any cells are filled with a certain color

Place this macro in the code module for ThisWorkbook. It uses the ColorIndex number 3 which is red. Change the number to suit your needs. It will detect only the first cell that is color filled. If you want to detect all of them, delete the 'Exit Sub' line. If you do this and you have many that are color filled, you will get a warning for each cell. It also will prevent the workbook from being closed if any cell is color filled otherwise the file could be closed without saving it and you would lose any changes made to the data.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.Interior.ColorIndex = 3 Then
            MsgBox ("Cell with color fill found in cell " & rng.Address(0, 0))
            Cancel = True
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.Interior.ColorIndex = 3 Then
            MsgBox ("Cell with color fill found in cell " & rng.Address(0, 0))
            Cancel = True
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Conditional formatting does present a problem. Provided that you have only one conditional formatting rule, the following should work. Again, you may have to change the "255" (RGB red) to suit your needs.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.FormatConditions(1).Interior.Color = 255 Then
            MsgBox ("Cell with color fill found in cell " & rng.Address(0, 0))
            Cancel = True
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.FormatConditions(1).Interior.Color = 255 Then
            MsgBox ("Cell with color fill found in cell " & rng.Address(0, 0))
            Cancel = True
            Application.ScreenUpdating = True
            Exit Sub
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

I'm not sure what is happening. Picking up conditional formatting is tricky. I can't promise a solution but could you post a copy of your file and I'll have a look. If it contains any confidential information, you could replace it with generic data.