Validation List Advise Please
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 |
Validation List Advise Please
Assuming you're using conditional formatting to change the cell color,
set the font to the same color as the background for "Blank". In article , Dermot wrote: 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 |
Validation List Advise Please
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" wrote: 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 |
Validation List Advise Please
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 "Elkar" wrote: 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" wrote: 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 |
All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com