Formatting Question

[RealEstate]

Hi, new to the board, glad to be here.

I'm a real estate broker and I'm basically setting up a commission
calculator and was wondering if there's any way to change the number
category for a particular cell based on an "if" function. In other
words, if A6 equals "rental (Q6)" then the category of C6 changes to
"currency". If A6 equals "sale (R6)" then the category of C6 changes
to "percentage". Is there any type of advanced conditional formatter?
Do I have to use VB?

I don't know if that makes sense or not, hopefully it does. Thanks in
advance for any help

Rich



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Dave Peterson]

It sounds as though C6 contains a formula, if that's true and you could live
with the text representation (not numbers anymore), maybe you could modify your
formula in C6:

=IF(A6="rental (q6)",TEXT(yourformula,"$#,##0.00"),TEXT(yourformula,"00.0%"))
or just use a helper cell (I chose A1) and:
=IF(A6="rental (q6)",TEXT(A1,"$#,##0.00"),TEXT(A1,"00.0%"))

But if you want to change the numberformat, you'll need a macro.

If you right click on the worksheet tab that should behave this way. Select
view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a6")) Is Nothing Then Exit Sub

If LCase(Target.Value) = "rental (q6)" Then
Range("c6").NumberFormat = "$#,##0.00"
Else
Range("C6").NumberFormat = "0.00%"
End If

End Sub

And if you're new to macros, you can read some notes at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[RealEstate]

thanks!!



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[RealEstate]

Maybe I spoke to soon. I tried the code, but it didn't work. I input
the following:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B6")) Is Nothing Then Exit Sub

If LCase(Target.Value) = ("X6") Then
Range("F6").NumberFormat = "$#,##0.00"
Else
Range("F6").NumberFormat = "0.0%"
End If

End Sub

Basically, if B6 equals the text Rental or X6, then I want a dollar
format. If B6 equals the text Sale or Y6, the I want a percentage
format. What am I doing wrong? Help!!



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[RealEstate]

hey guys, just wondering if anyone had any idea what i could be doing
wrong with this. any ideas?

 

[Dave Peterson]

You had one reply:

http://groups.google.com/[email protected]

 

[Dave Peterson]

Can you post the formula that didn't work?

And the data that was in the cells that the formula used, too.