Conditional Number Formatting a range based on a different cell value

[Edgarvelez]

Hi All,

Tried to record a conditional format but it's not working.
In cell C11 I have a drop down and when I select LB. I want to change the number format to #,##0.00 on range D21:E1048576

***************************************************
This the recorded code that does not work

Range("D21:E1048576").Select
Selection.NumberFormat = "#,##0.00"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C$11=""LB."""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,""#,##0.00"")"
Selection.FormatConditions(1).StopIfTrue = False
***************************************************
I added a screen shot to simplify the goal I and trying to get.

 

[Jeffrey Mahoney]

I don't know why recording a CF for Excel is so difficult. I would change the macro to this:

Dim Rng As Range
  Dim Cnt As Long
  
  Set Rng = Range("D21:E1048576")
  Rng.FormatConditions.Delete
  Rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$C$11=""LB."""
  Cnt = Rng.FormatConditions.Count
  Rng.FormatConditions(Cnt).SetFirstPriority
  Rng.FormatConditions(Cnt).NumberFormat = "#,##0.00"
  Rng.FormatConditions(Cnt).StopIfTrue = False

 

[Edgarvelez]

I don't know why recording a CF for Excel is so difficult. I would change the macro to this:

Dim Rng As Range
  Dim Cnt As Long
 
  Set Rng = Range("D21:E1048576")
  Rng.FormatConditions.Delete
  Rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$C$11=""LB."""
  Cnt = Rng.FormatConditions.Count
  Rng.FormatConditions(Cnt).SetFirstPriority
  Rng.FormatConditions(Cnt).NumberFormat = "#,##0.00"
  Rng.FormatConditions(Cnt).StopIfTrue = False

I don't know why recording a CF for Excel is so difficult. I would change the macro to this:

Dim Rng As Range
  Dim Cnt As Long
 
  Set Rng = Range("D21:E1048576")
  Rng.FormatConditions.Delete
  Rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$C$11=""LB."""
  Cnt = Rng.FormatConditions.Count
  Rng.FormatConditions(Cnt).SetFirstPriority
  Rng.FormatConditions(Cnt).NumberFormat = "#,##0.00"
  Rng.FormatConditions(Cnt).StopIfTrue = Fa
[/QUOTE]

 

[Edgarvelez]

Awesome it worked and thank you very much!!!