If Statement Before Save

[scottnshelly]

I want a message box to pop up if there are any orange cells on sheet1.
The click event will be BeforeSave. I have some conditional formattin
that changes a cell to orange. I don't want the user to be able t
save if there is orange on the sheet. The orange can appear i
A1:F6000.

What does the If statement look like for this scenario?

Thanks

 

[Bob Phillips]

Tricky!

See http://www.xldynamic.com/source/xld.CFConditions.html

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"scottnshelly" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

This should do it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cell In Range("A1:F6000")
If cell.Interior.ColorIndex = 44 Then
MsgBox "Sheet has Orange Cells"
End If
Next
End Sub

Chas

 

[scottnshelly]

Thanks. That was really complicated. Would it be easier if I had a
message box if the same condition is met that causes the cells to turn
orange?
The conditional formatting in A:F is the same:
=IF(AND(H2<>"",A2=""),TRUE,FALSE)

If there is something in H but any one of A:F is blank, I want an error
message or prevent them from saving.

I have tried to make it clear that columns A:F are required, but they
are still leaving some of them blank.
Thanks.

 

[Bob Phillips]

CF Chas, not cell colour.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Have a little macro that checks it

Function AnyOrange()
Dim cell As Range

For each cell In range("A2:F6000")
If cell.value ="" and cell.Offset(0,7) <> "" Then
AnyOrange = True
Exit Function
End If
Next cell
End Function

Then just see if the function returns True and don't save if so.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"scottnshelly" <[email protected]>
wrote in message
news:[email protected]...

 

[scottnshelly]

Thanks a lot.
That did the trick.
Keep up the good work.