Struggling with this one. In-line Cell formatting again

TJMorgan · #1 02-16-2017, 07:06 AM

Senario: Cell A20 contains fixed test. Cell B20 next to it contains a drop down list of six items with a blank space at the end (','). I need the fixed text in A20 to turn grey (ghost out) when 'blank' is chosen from the dropdown menu in B20. Ahrrrr. Driving me crazy!

NoSparks · #2 02-16-2017, 08:23 AM

You're not telling us what you've tried.
I'm guessing Conditional Formatting.
Have you tried using the Worksheet_Change event?

TJMorgan · #3 02-16-2017, 08:31 AM

No I haven't tried that. Fairly new to Excel at this level. I tried formating from B20 - then realised it had dropdown text in it. D20 in the same row contains only manual insertion of figures. When empty this would seem the logical cell to format to invoke Conditional Formting in Cell A20. In other words 'If I'm empty change text in Cell A20 to grey.

NoSparks · #4 02-16-2017, 08:39 AM

You're still not saying what you've tried.

based on " six items with a blank space at the end (',') "
I'm not quite sure what a blank is, that looks like a comma to me

Conditional formatting (for a blank) would be =ISBLANK(B20)

TJMorgan · #5 02-16-2017, 08:50 AM

When creating a dropdown list using DATA/Data Validation, the 'comma/single quote/comma' creates a blank line at the end of the list. In other words if I don't want any items in the list to appear, this creats and empty cell. I have added a bit more explanation in the previous post.

TJMorgan · #6 02-16-2017, 09:26 AM

Just to show what I am trying to do here, I have added a screen grab (I hope). When D32 is empty (it's the figures column, as B32/C32 are joined), I would like the text in A32 (Filter) to become grey (ghost text). If figures are then added back into D32, the text in A32 returns to black. Hope that makes sense.

NoSparks · #7 02-16-2017, 09:26 AM

That's interesting...

What shows at the bottom of the drop down isn't blank,
what shows in the formula bar is the single quote
doing a len() calculation of the cell gives zero
and running this little code I use to see what's actually in a cell returns nothing

Code:

Sub CheckOfCharacters()
    Dim i As Integer
    Dim str As String

str = ActiveCell.Value

    For i = 1 To Len(str)
        Debug.Print Mid(str, i, 1) & "  =  " & Asc(Mid(str, i, 1))
    Next

End Sub

I guess the work around would be to put your list into a column somewhere and in the validation for the list specify the range plus the cell on the next row which would be empty.

TJMorgan · #8 02-16-2017, 10:02 AM

Okay - see where you're going. However, ignore the highlighted cell on the screen grab - my error. The cell on the right of it - above the two figures, is the one I want to format to change the text colour to grey in A32. No figures in D32 - text grey on A32. Figures in D32, text returns to black in A32. There must be a way to do it - I just can't see it!

NoSparks · #9 02-16-2017, 10:28 AM

I'll be away from the computer for a while. If you'd like to post a spreadsheet instead of a picture of a spreadsheet I'll have a look when I get back.

TJMorgan · #10 02-16-2017, 10:37 AM

Okay. Thanks NoSparks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formatting unprotected cell in protected w/s makes the cell locked?	tango356	Excel	2	01-23-2017 02:13 PM
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting	hanvyj	Excel Programming	0	08-28-2015 01:15 AM
Question re: Conditional formatting (cell occurs after another cell)	Saywarder	Excel	1	04-20-2015 11:29 AM
Conditional Cell Formatting: Alternating Cell Checks	corbott	Excel	4	12-16-2014 01:51 PM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM