Can cells change color when others have entries? And other tricks too???

Hi again Bret,

For your first wish the answer is yes!!

Select cell A1 and go to Format>Conditional Formatting.

Select "Formula is" then type this formula:
=B1>0
Click "Format" and either set the font or backgound color. Click OK and OK again

Number 2 is also Yes!

In any 3 cells put the words: Open blank Close
Name this range ListRange e.g Insert>Name>Define

Select any cell and go to Data>Validation
Select "List" then type: =ListRange

Set the Input Message and Error Alert to the level required. Click OK.

Do the same as above for you time entries, but when you name your range go beyond the last row and include some blanks. This way you can easily add new list entries. Data Validation wont show the blank rows.

Now, for color change list the best that can be done is to Conditionally Format the cells as we did to start with.

I'll be right back with an answer to your last question.

Dave

OzGrid Business Applications

Ok, here we go!

Push Alt+F11, Then go to Insert>Module and Paste in this code:

Sub ClearIt()
Sheets("Sheet1").Range("A1") = ""
End Sub

Change the sheet name and cell address to suit.
Push Alt+Q.
Right click on your sheet name tab and select "View Code"
Paste in this code over the top of what you see (not the other scroll code though )

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells > 1 Then Exit Sub
If Target.Address = "$A$1" And Target <> "" Then
Application.OnTime Now + TimeValue("01:00:00"), "ClearIt"
End If
End Sub

Push Alt+Q again.

Now whenever anything is put into cell A1 on Sheet1 the code will fire and automatically run the "ClearIt" code 1 hour later.

Dave

Oh, BTW There was pasting problem with the Scroll code I gave you, but I have fixed it up:

'11791.html

OzGrid Business Applications

Everything looks good, but...Help!

Hi Dave,

First, I cannot thank you enough for all your help!
The folks down at the center are thrilled with the
status board. But now I'm having some troubles.
First, and this is the big one, I can't get the
cells to show the drop down boxes any more even
tho everything looks right in the Data/Validation
area. It was working before, but now the cells still
accept regular text, and don't offer the drop
list in any cell. Also, and this may be related,
the anti scroll code does not want to work. I took
it back out, and it looks the same as before, i.e
one line saying
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range),
and the next one blank and the next one saying
EndSub
, but when I close the sheet and reopen it it still
says there are macros in the sheet. Can these problems be related?
What the heck did I screw up? If I open a new blank sheet the
dropdowns work, and also in my older versions of this. I am
uploading the sheet as Sia6.xlt, feel free to have a look
at it if you want, it's linked below.

Thanks again, so much for your help, Dave!

Bret

OK, just about got it, thanks Dave!

Hey,

I just about got it done, Dave, by starting over on a new sheet to get past those anomalous problems
I ran into with the unfindable macro, got my drop downs all fixed up, and the time thing is pretty
unworkable 'cause the times in the cells last for different times. BUT, it is totally cool and I wonder
if you'd like to have a look at it and see if you have any thoughts or suggestions.

Thanks so much!!
Bret

Re: OK, just about got it, thanks Dave!

I just about got it done, Dave, by starting over on a new sheet to get past those anomalous problems

Sure Bret

Could you e-mail it to me, I'll take a look Today sometime.

Dave
OzGrid Business Applications