VBA to Loop Through Cells and Apply Conditional Formatting Based on Current Value Not Applying Correctly
I'm following best practices but I keep running into I'm trying to apply conditional formatting to a range of cells in Excel using VBA, but the formatting isn't being applied as expected... I want to highlight cells in a specific range (let's say A1:A10) based on their values. If the value is greater than 50, the cell should turn green; if it's less than or equal to 50, it should turn red. I've written the following code: ```vba Sub ApplyConditionalFormatting() Dim rng As Range Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10") ' Clear existing conditional formats rng.FormatConditions.Delete ' Add conditional formatting for values greater than 50 With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:=50) .Interior.Color = RGB(0, 255, 0) ' Green End With ' Add conditional formatting for values less than or equal to 50 With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLessEqual, Formula1:=50) .Interior.Color = RGB(255, 0, 0) ' Red End With End Sub ``` When I run this subroutine, the cells are not getting formatted at all. I've checked to ensure that the values in the cells are indeed numeric, and I've even added a message box to confirm that the loop is running, but it appears that the conditional formatting is simply not applying. Is there something I'm overlooking, or a better approach to achieve this? I've also considered switching from `xlCellValue` to using a formula for more complex conditions, but for now, I want to keep it simple. I'm using Excel 2019. Any help would be greatly appreciated! This is part of a larger CLI tool I'm building. This issue appeared after updating to Vba 3.9. Is there a better approach? This is part of a larger desktop app I'm building. Any help would be greatly appreciated!