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 formatting that changes a cell to orange. I don't want the user to be able to save if there is orange on the sheet. The orange can appear in 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]...
>
> 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 formatting
> that changes a cell to orange. I don't want the user to be able to
> save if there is orange on the sheet. The orange can appear in
> A1:F6000.
>
> What does the If statement look like for this scenario?
>
> Thanks.
>
>
> --
> scottnshelly
> ------------------------------------------------------------------------
> scottnshelly's Profile:
http://www.excelforum.com/member.php...fo&userid=7301
> View this thread: http://www.excelforum.com/showthread...hreadid=570535
>

[ChasAA]

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" wrote:

>
> 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 formatting
> that changes a cell to orange. I don't want the user to be able to
> save if there is orange on the sheet. The orange can appear in
> A1:F6000.
>
> What does the If statement look like for this scenario?
>
> Thanks.
>
>
> --
> scottnshelly
> ------------------------------------------------------------------------
> scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301
> View this thread: http://www.excelforum.com/showthread...hreadid=570535
>
>

[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)

"ChasAA" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> >
> > 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 formatting
> > that changes a cell to orange. I don't want the user to be able to
> > save if there is orange on the sheet. The orange can appear in
> > A1:F6000.
> >
> > What does the If statement look like for this scenario?
> >
> > Thanks.
> >
> >
> > --
> > scottnshelly
> > ------------------------------------------------------------------------
> > scottnshelly's Profile:
http://www.excelforum.com/member.php...fo&userid=7301
> > View this thread:
http://www.excelforum.com/showthread...hreadid=570535
> >
> >

[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]...
>
> 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.
>
>
> --
> scottnshelly
> ------------------------------------------------------------------------
> scottnshelly's Profile:
http://www.excelforum.com/member.php...fo&userid=7301
> View this thread: http://www.excelforum.com/showthread...hreadid=570535
>

[scottnshelly]

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