PDA

View Full Version : Change cells number format off an other cells value



mduff
11-01-2013, 05:44 PM
I am using an if statement that depending on the value in B1 is a 1 or a 2 will ether return a number or a Percentage. I am trying to get excel to change the format if the value in b2 is 1 to a % format and if it is 2 to a number format.

I am thinking this could be done with a change event macro but I am not sure how to set it up or if there is a better way

any help would be appreciated

GarysStudent
11-02-2013, 10:11 AM
Let's say the cell with the formula is cell H7 and that cell B1 contains the typed value 1 or 2. Use the following event macro:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim H7 As Range, B1 As Range
Set H7 = Range("H7")
Set B1 = Range("B1")
If Intersect(Target, B1) Is Nothing Then Exit Sub
Application.EnableEvents = False
If B1.Value = 1 Then
H7.NumberFormat = "0.00%"
Else
H7.NumberFormat = "General"
MsgBox 2
End If
Application.EnableEvents = True
End Sub

mduff
11-03-2013, 12:57 PM
Thanks a lot but if B1 is the result from a Combo Box selection and not typed?

astranberg
11-04-2013, 09:49 AM
That seems fairly complex. Comboboxes aren't technically at a cell location (ie. B1). I would suggest having your comboboxes in order such that ComboBox1 is at B1, ComboBox2 is at B2, etc.
Additionally, you might consider doing a checkbox if you're only alternating between percentage and number format.



r = 0
For Each cb In ActiveSheet.CheckBoxes
r = r + 1
If cb.Value = 1 Then ' True
Cells(r, 1).NumberFormat = "0.00%"
Else
cells(r, 1).NumberFormat = "General"
End If
Next cb