conditional custom formating question

  • Thread starter Thread starter doublet83
  • Start date Start date
D

doublet83

Hi, I need to be able to type in "B" or "S" or "BC" or "SS" into a cel
and have the same cell return "Buy" or "Sell" or "Buy to Cover" o
"Short Sell" respectively.

Is there any way to do this? Thanks
 
Hi
this would require VBA (using an event procedure) Would this be a
feasible way for you?
 
Double,

You could use Autocorrect to do this, but it would apply to all cells, and
in Word and Powerpoint, etc, and drive you utterly mad. Or a macro could do
it.

If you want a macro to do it, specify if the cell is a certain cell, or any
in a column. We'll write you one.
 
Using AutoCorrect
would also mean that you can't really type anything
because every word with B or S would be changed.

An example of Conditional Formatting can be found in
http://www.mvps.org/dmcritchie/excel/event.htm#case
suggest reading starting at the top.

VBA is case sensitive.

The macro below is modified to your request.
Unlike regular macros this Event Macro will only apply to
the one worksheet. To install
right click on the sheet tab, view code, insert your coding.

The following will affect only columns B & D.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, modified from 2000-08-08 example in
' http://www.mvps.org/dmcritchie/excel/event.htm#case
Dim vText As String
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("B:B,D:D"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
' "Buy" or "Sell" or "Buy to Cover" or "Short Sell"
vText = cell.Value 'UCase(Trim(cell.Value))
Select Case Trim(UCase(cell.Value))
Case "B"
vText = "Buy"
Case "BC"
vText = "Buy to Cover"
Case "S"
vText = "Sell"
Case "SS"
vText = "Sell Short"
End Select
If vText <> UCase(Trim(cell.Value)) Then
Application.EnableEvents = False 'should be part of Change macro
cell.Value = vText
Application.EnableEvents = True 'should be part of Change macro
End If
Next cell
End Sub


Earl Kiosterud said:
Double,

You could use Autocorrect to do this, but it would apply to all cells, and
in Word and Powerpoint, etc, and drive you utterly mad. Or a macro could do
it.

If you want a macro to do it, specify if the cell is a certain cell, or any
in a column. We'll write you one.
Click to expand...
 
I think that it has to be a complete word for autocorrect to jump in.

(followed by a space or punctuation or enter and have a leading space or
nothing)

David said:
Using AutoCorrect
would also mean that you can't really type anything
because every word with B or S would be changed.

An example of Conditional Formatting can be found in
http://www.mvps.org/dmcritchie/excel/event.htm#case
suggest reading starting at the top.

VBA is case sensitive.

The macro below is modified to your request.
Unlike regular macros this Event Macro will only apply to
the one worksheet. To install
right click on the sheet tab, view code, insert your coding.

The following will affect only columns B & D.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, modified from 2000-08-08 example in
' http://www.mvps.org/dmcritchie/excel/event.htm#case
Dim vText As String
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("B:B,D:D"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
' "Buy" or "Sell" or "Buy to Cover" or "Short Sell"
vText = cell.Value 'UCase(Trim(cell.Value))
Select Case Trim(UCase(cell.Value))
Case "B"
vText = "Buy"
Case "BC"
vText = "Buy to Cover"
Case "S"
vText = "Sell"
Case "SS"
vText = "Sell Short"
End Select
If vText <> UCase(Trim(cell.Value)) Then
Application.EnableEvents = False 'should be part of Change macro
cell.Value = vText
Application.EnableEvents = True 'should be part of Change macro
End If
Next cell
End Sub
Click to expand...
 
You're right. If Auto Correct changed every letter "b" to
"Buy" it wouldn't work as well as an April Fool joke.
 
Other than VBA and the AutoCorct options there are 2 other possibles.

In cells A1:A4 enter

"Buy", "Sell", "Buy to Cover" and
"Short Sell" respectively.

Now hide these rows. Select cell A5, right click and "Pick From List".
This could be automated so VBA fires the "Pick From List" when you
select the cell.

Go to data>Validation and choose "List". Enter;
Buy, Sell, Buy to Cover,Short Sell
into the Source box.






** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
 
Back
Top