Excel 2000/Conditional Formatting for number type 2

[MJamison07]

Good Morning Excel Gurus -

I am creating a form and I am stumped. I hope there is a way I can do this. I want to format the number type in a cell based on the selection (using validation/list) in an adjacent cell.

If "Fixed" is selected, then I want the next cell to format as currency
If "%" is selected then I want the next cell to format as percentage
If "Remain" is selected, I want the next cell to remain blank (no entry allowed)

Thanks

 

[DaleWatson123321]

MJ,

Unless Excel 2000 has additional Conditonal Formatting options, it won't be possible using Conditional Formatting. My Excel 97 does not have "Number Format" as an option.

An alternative would be to use a Workbook-Open event for the sheet. I've written and tested the following routine, and it works fine.

This requires that you create the range name "InputRange" for those input cells. The cells should be in a contiguous range.

==========================
Dim num As String
Dim InputRange As Range

Private Sub Worksheet_SelectionChange(ByVal InputRange As Excel.Range)

num = ActiveCell.Offset(0, 1).Value

If num = "Fixed" Then
ActiveCell.NumberFormat = "0.00_);(0.00)"
ElseIf num = "%" Then
ActiveCell.NumberFormat = "0.00%"
ElseIf num = "Remain" Then
ActiveCell.ClearContents
End If

End Sub
==========================

I hope you find this a workable alternative. Please advise as to how you make out. If you like, I can email you the file I created.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[MJamison07]

Thanks Dale.

It's working!!! Except my input range is to the right of the "Fixed" etc cells. How do I make that adjustment?

 

[MJamison07]

Got it to work to the right --- except after I protect the worksheet. Any way around that?

Thanks

 

[DaleWatson123321]

MJ,

I've tried to modify the following line, to get it to refer to the cell to the LEFT.

num = ActiveCell.Offset(0, 1).Value

However, it wont take a "-" (minus) character for the column offset - i.e. (0, -1)

I've also tried the "Cells" option, but to no avail.

Can someone else PLEASE help out.

Thanks.

...Dale Watson dwatson@bsi.gov.mb.ca