Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop-down list validation won't allow a different worksheet | Excel Worksheet Functions | |||
Expanding Data validation from List | Excel Discussion (Misc queries) | |||
validation list with drop down list of options?? | Excel Worksheet Functions | |||
Auto Update A Validation List | Excel Worksheet Functions | |||
list validation using list validation... | Excel Worksheet Functions |