simple macro to format numbers doesnt work

i have the 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

Try this:

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")
    rg.FormatConditions(1).NumberFormat = "0.0"
    rg.FormatConditions(2).NumberFormat = "0.00"
    
    End With
   
End Sub

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Works. Thank you!!!

Originally Posted by David A Coop

Try this:

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")
    rg.FormatConditions(1).NumberFormat = "0.0"
    rg.FormatConditions(2).NumberFormat = "0.00"
    
    End With
   
End Sub

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Originally Posted by fruitloop44

Works. Thank you!!!

Good to hear!

Please don't forget to mark as SOLVED etc.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".