Validation List Advise Please

[Dermot]

I have a cell which has a validation List "Yes", "No" etc........
The selections produce different cell colours Yes = Yellow, No = Red
When the cell changes colour the appropriate text "Yes" or "No" is entered
in the cell.
I want to enter an option in the list "Blank" to remove the coloured
formatting.....the problem is the text "Blank" from the validation list is
entered into the cell......I don't want text in the cell.
How I enter "Blank" in the Validation list, and not have it appear as text
in the cell when selected?
Thanks

 

[JE McGimpsey]

Assuming you're using conditional formatting to change the cell color,
set the font to the same color as the background for "Blank".

 

[Elkar]

You could change the text color to White (or whatever your background color
is). This will allow "Blank" to be selected, but it will appear as though
nothing is in the cell.

Or, if that won't work for you, then you could change "Blank" on your list
to just an apostrophe '. A leading apostrophe in a cell will not be
displayed. It tells Excel to treat all following data as text. So, a lone
apostrophe will appear blank.

HTH,
Elkar

 

[Dermot]

Thanks Elkar
I am using the code below, the apostrophe worked fin and gives the result I
am looking for but would like to display a word in the list...i.e. "Blank" or
"Undo".....so others understand the option.....any other suggesions would be
appreciated?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("B6:M10000")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("B" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("B" & i & ":M" & i).Interior.ColorIndex = 3
Case "Remortgage"
Range("B" & i & ":M" & i).Interior.ColorIndex = 28
Case "Blank"
Range("B" & i & ":M" & i).Interior.ColorIndex = Null
Case "Y"
Range("B" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("B" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("B" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub