Have a cell filled black when blank in dropdown list

Aussie_81 · #1 04-01-2017, 07:04 PM

Does anyone know how to have an empty cell in a dropdown list set to colour black (rather than empty)?

I need it black as it is used to separate reoccurring information of a data set.

jeffreybrown · #2 04-01-2017, 07:08 PM

Hello & Welcome to the Forum,

You can use CF...

Conditional Formatting

Highlight applicable range >> A1
Home Tab >> Styles >> Conditional Formatting >> New Rule
Select a Rule Type: Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true: =LEN(A1)=0
Format… [Number, Font, Border, Fill]
OK >> OK

Aussie_81 · #3 04-01-2017, 09:06 PM

I have done as described and the dropdown list still only has a blank cell and not a black filled cell.

I can't attach an image for you to see as it appears the forum only allows http links.

The "Conditional Formatting Rule Manager" has:

Formula: ="LEN(B*)=0"
Format: is a black block
Applies to: =$B$8

The dropdown list values are in one sheet and the sheet that uses the dropdown list that the values are referenced are in another if that is of any relevance?

jeffreybrown · #4 04-02-2017, 06:35 AM

Quote:

Formula: ="LEN(B*)=0"

Not sure what's going on with your formula as it should be =LEN(B8)=0 and no quotes

From the FAQ...

Quote:

To attach a file to a new post, simply click the [click Go advanced - Manage attachments] button at the bottom of the post composition page, and locate the file that you want to attach from your local hard drive.

Aussie_81 · #5 04-03-2017, 12:33 AM

Please see below

Aussie_81 · #6 04-03-2017, 12:36 AM

Thanks Jeffrey Brown,

I used the apostrophises to represent the exact value stated, however I didn't check the post as I must have pressed shift 8 instead of just 8.

Please see the attached images (thanks for that).

Pecoflyer · #7 04-03-2017, 04:50 AM

Perhaps post a small sample sheet ( Go advanced - manage attachments)

Aussie_81 · #8 04-04-2017, 02:27 PM

Here is the spreadsheet I was referring to.

Note: When in the spreadsheet "STN1" and on row 15, column B, if the user selects the very last value from the dropdown list (under STN4) the population is blank rather than black.

The sheet "Data List" is where I have the formulas pertaining the "STN1" sheet

Logit · #9 04-04-2017, 04:03 PM

.
.
Click B12

Click Conditional Formatting on Menu Bar

Select "Format only cells that contain" / Equal to

In the formula field enter =("")

Format color for BLACK

Click Ok / APPLY /OK

Test B12 by selecting the blank item

When confirmed it works ... Click B12 again.

Click FORMAT PAINTER on HOME TAB in the CLIPBOARD section

Left click and hold on B12, then drag down the column as far as you need.

Aussie_81 · #10 04-05-2017, 01:47 AM

Thanks Logit,

I have got the spreadsheet to work as you have instructed but the functionality is not as I require it.

The purpose of the black cell is that it will be used as an observation set separator that the user will specify.

The data will then be read into Matlab for manipulation and reduction based on the users input through iterations.

Is there a way to allow the user to set the cell to black (through the dropdown list) rather than having them pre-set to black then require changing (overwrite)?

This is also important for the usability in the Matlab program.

Thankyou in advance.

Logit · #11 04-05-2017, 04:29 AM

Hmm ... I'm not familiar with MatLab.

At the moment I can't think of a way to cause the cell to turn black only when the blank
selection is ... selected. Perhaps someone else knows of a way.

Suggestion: Surely MatLab must have a blog or Forum ? Have you asked there ? Users of the program must have already done the same thing ?

NoSparks · #12 04-05-2017, 06:33 AM

I realize this is not posted under Excel Programming and this will require the file to be a macro enabled .xlsm, but I would remove the conditional formatting and use the Worksheet_Change event as it triggers when a selection is made in a validation drop down.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Or Target.Column <> 2 Then Exit Sub
If Not Intersect(Target, Me.Range("B12:B146")) Is Nothing Then
    If Target.Value = "" Then
        Target.Interior.ColorIndex = 1
    Else
        Target.Interior.ColorIndex = 0
    End If
End If

End Sub

Logit · #13 04-05-2017, 12:55 PM

No Sparks:

I am so glad when my mind stops functioning ... you can save me !

Thanks !

Aussie_81 · #14 04-06-2017, 12:44 AM

Hey No Sparks,

Thankyou for the that.

That is exactly what I wanted.

Much appreciated.

NoSparks · #15 04-06-2017, 04:50 AM

You're welcome, glad I could assist.
Thanks for reporting back.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formulato say if cell is blank do this, if not blank do this.	mbesspiata	Excel	1	01-17-2015 05:02 AM
Using word art in a table with a filled cell	halfnite	Word	5	10-07-2014 05:24 PM
Auto add a list cell if the last list cell is filled	tasuooooo	Excel	1	07-31-2012 08:40 PM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM
Forms: combination of a list and a text field to be filled in	bart014	Word	0	04-23-2010 12:55 AM