#1
|
|||
|
|||
Struggling with this one. In-line Cell formatting again
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!
|
#2
|
|||
|
|||
You're not telling us what you've tried.
I'm guessing Conditional Formatting. Have you tried using the Worksheet_Change event? |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
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.
|
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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!
|
#9
|
|||
|
|||
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.
|
#10
|
|||
|
|||
Okay. Thanks NoSparks.
|
Tags |
conditional formatting, drop down menu, if formatting |
|
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 |