VBA Check for interior cell color before close

[Pinaceous]

Hi All,

I'm working with a

Private Sub Workbook_Close()

code, where I would like to provide the code to check for an interior cell color red in the range of B11:AI180.

I was experimenting with the following but I cannot make it work.

Dim response As VbMsgBoxResult
response = MsgBox("Highlighted cells are found on your sheet.  Do you want them to be corrected", vbYesNo)

   If response = vbYes Then
    MsgBox "Please make the corrections."
  Exit Sub

   If response = vbNo Then
     'MsgBox "Continues to close document."
   Exit Sub

End If

Do you think you can help?

Many thanks!
-Pinaceous

 

[jolivanes]

How were the cells colored? "Conditional Format" or "Format Cells"?

 

[Pinaceous]

Hi jolivanes,

They are colored through VBA criteria.

Mostly cells that are supposed to be filled in by the user but may have been left blank, where it triggers a corresponding cell to interior color red.

So, with multiple users using the same document, the red cells can be overlooked.

That is why I'm trying accomplish this code now.

Thanks!
pinaceous

 

[jolivanes]

Put this in the "ThisWorkbook" module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, rngCol As Range
    For Each c In Range("B11:AI180")
        If c.Interior.Pattern <> xlNone Then
            If rngCol Is Nothing Then
                Set rngCol = c
                    Else
                Set rngCol = Union(rngCol, c)
            End If
        End If
    Next c
    If rngCol Is Nothing Then
        MsgBox "No colored cells in range!"
            Else
        MsgBox "Found " & rngCol.Count & " colored cells in:" & vbLf & rngCol.Address
    End If
End Sub

 

[Pinaceous]

Hey thanks jolivanes!

I'll try it out now! Thanks so much!

 

[Pinaceous]

Hey jolivanes!

It works really great! But do you know if you can add, if the code finds a interior red colored cell that it can possibly stop the closing of the workbook?

Is this possible?

Thanks,
pinaceous

 

[jolivanes]

Do you want to keep it open or just get rid of the colors?

 

[jolivanes]

Maybe you want a choice of "uncoloring" or not.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, rngCol As Range, msg1 As String
    For Each c In Range("B11:AI180")
        If c.Interior.Pattern <> xlNone Then
            If rngCol Is Nothing Then
                Set rngCol = c
                    Else
                Set rngCol = Union(rngCol, c)
            End If
        End If
    Next c
    If rngCol Is Nothing Then
        MsgBox "No colored cells in range!"
            Else
        MsgBox "Found " & rngCol.Count & " colored cells in:" & vbLf & rngCol.Address(0, 0)
    End If
    msg1 = MsgBox("Undo the colors in the colored cells?", vbYesNo, "Keep Colored Cells Or Not.")
        If msg1 = vbYes Then
            rngCol.Interior.Pattern = xlNone
                Else
            MsgBox "We'll continue closing this Workbook."
        End If
End Sub

 

[Pinaceous]

Hey jolivanes,

That is great!

Do you know in lieu of

rngCol.Interior.Pattern = xlNone

that the workbook can be prevented from closing alltogether?

Is this possible?

Thanks!
pinaceous

 

[jolivanes]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, rngCol As Range, msg1 As String
    For Each c In Range("B11:AI180")
        If c.Interior.Pattern <> xlNone Then
            If rngCol Is Nothing Then
                Set rngCol = c
                    Else
                Set rngCol = Union(rngCol, c)
            End If
        End If
    Next c
    If rngCol Is Nothing Then
        MsgBox "No colored cells in range!"
            Else
        MsgBox "Found " & rngCol.Count & " colored cells in:" & vbLf & rngCol.Address(0, 0)
        Cancel = True
    End If
End Sub