Excel Formula To Do/Undo 'Strike-Through'

[James Perry]

I have a drop-down list of things that we sell.
Sometimes due to unavoidable delays a particular item may not be available and so it becomes necessary to take the item off the list in which case i would like to use a 'strike-through' on the item (in the drop-down list) to indicate that a particular item is currently not available and later on take off the 'strike-through' when the item becomes available.
Thank you for a suitable solution.
God bless.

 

[chirayu]

There is no formula to do that but you can use CTRL + 5 to apply and remove
Also I have added another link for dropdown application - not sure how it works

http://office.microsoft.com/en-in/excel-help/format-text-as-strikethrough-HP001216427.aspx
http://www.mrexcel.com/forum/excel-questions/612916-strikethrough-formula.html

 

[Faseeh]

@chirayu

The OP wants entries in a drop down menu to be stroked through if the corresponding data is not present. when it is present it should not be done so..

 

[Ranjith kumar]

Hi James,
It can be done using VBA with a small code



Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Variant
Set rng = ActiveSheet.Range("B2:B5")
For Each cell In rng
If (cell.Value = "No") Then
cell.Offset(0, -1).Font.strikethrough = True
Else
cell.Offset(0, -1).Font.strikethrough = False
End If
Next cell
End Sub

 

[Somendra Misra]

@Ranjith kumar

What you are doing can be done with simply Cond. Formatting. But OP wants if say Computer is not available it should be strike out in Drop down list.

I don't think that is possible in data validation through normal means.

Regards,

 

[chirayu]

Not possible in dropdown as far as In know - unless you use a data validation list as its cell based

 

[James Perry]

Very many thanks guys.........
@Ranjith kumar: I'm a VB aspirer and that VBA looks really good. Thank u.

 

[James Perry]

@chirayu: Thank u and thank u 4 the links.