If Statement Before Save
 

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

--
scottnshell
-----------------------------------------------------------------------
scottnshelly's Profile: http://www.excelforum.com/member.php...nfo&userid=730
View this thread: http://www.excelforum.com/showthread.php?threadid=57053

If Statement Before Save
 

Tricky!

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

--
HTH

Bob Phillips

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

"scottnshelly"
wrote in message
news:scottnshelly.2ccezi_1155244811.1027@excelforu m-nospam.com...

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

If Statement Before Save
 

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

If Statement Before Save
 

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

If Statement Before Save
 

CF Chas, not cell colour.

--
HTH

Bob Phillips

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

"ChasAA" wrote in message
...
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

If Statement Before Save
 

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"
wrote in message
news:scottnshelly.2cciov_1155249612.0315@excelforu m-nospam.com...

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

If Statement Before Save
 

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


--
scottnshelly
------------------------------------------------------------------------
scottnshelly's Profile: http://www.excelforum.com/member.php...fo&userid=7301
View this thread: http://www.excelforum.com/showthread...hreadid=570535