Formatting Question

RealEstate

unread,

Aug 25, 2003, 8:56:24 PM8/25/03

to

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

------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

Dave Peterson

unread,

Aug 25, 2003, 11:11:59 PM8/25/03

to

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

--

Dave Peterson
ec3...@msn.com

RealEstate

unread,

Aug 26, 2003, 2:07:01 AM8/26/03

to

thanks!!

RealEstate

unread,

Aug 26, 2003, 3:07:45 AM8/26/03

to

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!!

RealEstate

unread,

Aug 27, 2003, 6:57:59 PM8/27/03

to

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

Dave Peterson

unread,

Aug 27, 2003, 7:55:34 PM8/27/03

to

You had one reply:

http://groups.google.com/groups?threadm=3F4ACFFF.65CF831C%40msn.com

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

--

Dave Peterson
ec3...@msn.com

RealEstate

unread,

Aug 28, 2003, 1:06:14 AM8/28/03

to

i tried using your formula but to no avail. my cell would change to
percentage, but the other format wouldn't show. just thought i was
inputting the wrong info so i pasted exactly what i put in.

sorry if there was a reply to what i was doing wrong, i didn't see
anything. i'm still new to this forum and don't know all the little
nuances yet.

Dave Peterson

unread,

Aug 28, 2003, 5:32:53 PM8/28/03

to

Can you post the formula that didn't work?

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

--

Dave Peterson
ec3...@msn.com