I'm having trouble with conditional formatting a cell based on it's value (% or amount)

[ChristopherBaber]

I'm writing a calculator and one of the inputs could be either a percentage or an amount. I want the cell to format itself based on what it entered. I'm using conditional formatting with a formula that says if cell value >=1, then format as accounting, if cell value <1, then format as a percentage.

I have the cell base format set to General, expecting the conditional formatting to do what I want in the background.

This basically works, but if the existing value is a percentage (40%) and then I enter a value of 30000, it uses the existing % formatting to change this value to 300, then applies the conditional formatting, so it converts the 30,000 to $300 in the cell. it's not just a visual error, it actually removes the 2 zero's.

However, if I enter .4 into the cell, it converts to 40% properly, does NOT change the basic formatting of the cell to percentage, so when I enter 30000 into the cell, it does not remove the 2 zero's and leave the value at $30,000.

It's only when I enter 40% into the cell that it changes the base format to percentage, and then entering a number removes the 2 zero's.

So, I'm looking for a way to force the base formatting to stay as General, or some other way to do what I'm trying to do, that isn't susceptible to this glitch.

Buyer Options.xlsx
	M
7	$400
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M7	Cell Value	>=1	text	NO
M7	Cell Value	<1	text	NO

 

[ChristopherBaber]

sorry, i didn't see a way to edit the original post. After I posted I noticed that it's showing the cell format as text, so i wanted to post the actual conditional formatting as I have it in the cell, in case it was unclear.

 

[kevin9999]

You might be better off with a worksheet change event. If you want to try it, right click the sheet tab name, select view code, and copy the code below into the window that appears on the right of the screen. See if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("M7"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Target.Value2 < 1 Then
            Target.NumberFormat = "0%"
        Else
            If Target.NumberFormat = "0%" Then Target.Value = Target * 100
            Target.NumberFormat = "$#,##0"
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

When you enter .4 you get this:

test.xlsm
	M
7	40%
Sheet1

When you enter 30000 you get this:

test.xlsm
	M
7	$30,000
Sheet1

 

[ChristopherBaber]

Thank you for that.

I've closed the computer for the night so I can't test right now, but will in the morning.

But, my issue was only happening when I typed something with the %, like 40%

I kept searching, and trying things and discovered that if I change the basic format of the cell to number (instead of general), typing 40% does not force it to the percentage format and my conditional formatting does now work in all cases.

Simple, but elusive.