MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Posted by Bret on March 10, 2001 11:57 PM

Ok, since you guys have helped me so much already,
and yes, I am a rank beginner at this, I wonder if
you can help me out some more.

We are using Excel to display a status board at an Air Traffic
Control center, and are trying to make it both more user
friendly and more versatile. Can anyone tell me a simple way to
make a cell that displays plain text change color when an
adjacent cell has anything typed into it, that is, it is in a non-empty state.

And to add a couple more dreams I have for my form, is there
a way to have a blank (no entry) be a pickable option when
limiting cell entries to a drop-down list? I need to have the words
"open", "closed", and also a blank option all in the same drop-down. Also is
it possible to have the "open" and the "closed" be in different colors in the
same drop-down?

And the last, (oh wait, there's another after this, too)
and probably most remote possibilty, is there a way to have a drop-down
list that offers both pre-selected and user enterable values?
I'd like to make a drop down that offers, for example, the common entries
"330/280", and "300/280", and also offers the user an option to enter a custom value.

And the last, really the last one, is we have time values displayed on our status board that
specify a condition that will last for a certain time, for example, certain airspace will
be unavailable to civil traffic from 1930-2130 Zulu. Is there a way that I could program
Excel to clear a cell after a specified time period?

I know I'm probably dreaming, but then I've seen you guys show how to do some amazing things.
So got any tricks up your sleeves on this one?

There's a link to a sample of my form below if you want to have a look; it'll all become clear
what I need if you see it.

Thanks a bunch!


Posted by Dave Hawley on March 11, 2001 12:15 AM

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:
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.


OzGrid Business Applications

Posted by Dave Hawley on March 11, 2001 12:31 AM

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.


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


OzGrid Business Applications

Posted by Bret on March 11, 2001 1:30 PM

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
, 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!


Posted by Bret on March 11, 2001 4:28 PM

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
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!!

Posted by Dave Hawley on March 11, 2001 4:43 PM

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.

OzGrid Business Applications