simple macro to format numbers doesnt work

[helpsky]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px}</style>i have hte below code but it doesnt work for some reason! the code runs without breaking but it does not format at all. after running the code, if you look at the conditional formatting button in the ribbon, it shows that the formatting is there but i just doesnt apply it to any cells. all i want if for number greater than one to have 1 decimal place and number less than or equal to one to have two decimal places. please advise!!! thank you!!!!

Sub recent()
   
    sorter_col = Cells.Find("Spread", [A1], , , xlByColumns, xlPrevious).Column
  
    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition
    Set rg = Cells.Columns(sorter_col)
    Cells.Columns(sorter_col).Select
   
    
    'clear any existing conditional formatting
    rg.FormatConditions.Delete
 
    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=1")
    Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLessEqual, "=1")
 
    'define the format applied for each conditional format
    With cond1
    NumberFormat = "0.0"
    End With
 
    With cond2
    NumberFormat = "0.00"
    End With
   
End Sub

 

[pvr928]

Hi helpsky

I'm not aware of conditional formatting changing number formatting of cells. For that you need the 'Format Cells' function (right click on a cell -> 'Format Cells' or SHIFT + F10 -> 'Format Cells').

The following Worksheet_Change event (place the code in the worksheet's code sheet) operates on cell A1 shows this in action:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("A1") Then
    If Target.Value2 > 1 Then
    
        Target.NumberFormat = "0.0"
        
    Else
    
        Target.NumberFormat = "0.00"
        
    End If
    
End If

End Sub

Cheers

pvr928