Conditional Formatting

abcwen123

New Member
Joined
Jun 6, 2007
Messages
16
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?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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
AB
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
AB
1100.00%%
2200.00%
3300.00%
4400.00%
5500.00%
6600.00%
7700.00%
8800.00%
9900.00%
101000.00%
111200.00%
121300.00%
131400.00%
141500.00%
Sheet1


Excel Workbook
AB
11
22
33
44
55
66
77
88
99
1010
1112
1213
1314
1415
Sheet1
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
I prefer code for this
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top