Conditional Formatting

[abcwen123]

I'm trying to format a column of cells based on whether I selected percentage or $ from the drop-down list in the column to the right. Obviously I want the cell to be formated as currency if I selected $ and formatted as a percentage if I selected percentage.

I was able to get this to work initially with two overlapping conditional fomatting rules that used if statements [e.g. if($B1="$")]. However, if I go back to the cell I previously selected as $ and change it to percentage the formatting will not automatically change using the conditional formatting rules. I then have to re-enter the number in the first column to get the conditional formatting to re-run.

Is there another way I can accomplish this?

 

[c_m_s_jr]

I'm trying to format a column of cells based on whether I selected percentage or $ from the drop-down list in the column to the right. Obviously I want the cell to be formated as currency if I selected $ and formatted as a percentage if I selected percentage.

I was able to get this to work initially with two overlapping conditional fomatting rules that used if statements [e.g. if($B1="$")]. However, if I go back to the cell I previously selected as $ and change it to percentage the formatting will not automatically change using the conditional formatting rules. I then have to re-enter the number in the first column to get the conditional formatting to re-run.

Is there another way I can accomplish this?

I was able to get it to work:

My conditional formulas are:
1st: =$B$1="$"
2nd: =$B$1="%"

I am using 2007, but can't image that would create an issue

Excel Workbook
	A	B
1	$ 1.00	$
2	$ 2.00
3	$ 3.00
4	$ 4.00
5	$ 5.00
6	$ 6.00
7	$ 7.00
8	$ 8.00
9	$ 9.00
10	$ 10.00
11	$ 12.00
12	$ 13.00
13	$ 14.00
14	$ 15.00
Sheet1

Excel Workbook
	A	B
1	100.00%	%
2	200.00%
3	300.00%
4	400.00%
5	500.00%
6	600.00%
7	700.00%
8	800.00%
9	900.00%
10	1000.00%
11	1200.00%
12	1300.00%
13	1400.00%
14	1500.00%
Sheet1

Excel Workbook
	A	B
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	12
12	13
13	14
14	15
Sheet1

 

[lenze]

I prefer code for this

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("$H$1")
Case "Dollar": Range("G:G").Style = "Currency"
Case "Percent": Range("G:G").Style = "Percent"
Case Else
End Select
End Sub

or maybe

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 8 Then Exit Sub
Select Case Target
Case "Dollar": Cells(Target.Row,7).Style = "Currency"
Case "Percent": Cells(Target.Row,7).Style = "Percent"
Case Else
End Select
End Sub

You can create sustome styles if you need to
lenze