VBA conditional formatting and contents clearing

[Laura J]

Hi all,

May I please seek you hep with two VBA codes?

I would like to highlight grey (rgbLightGrey if possible) row range A2:AI2 if value of R2 < 0 and repeat this for every row in the table range A2:AI232.


Then, I'd like to delete content of cell (text & formulas) in range T2:AI2 if the conditional formatting is true and the cells are grey (or if the R2 < 0 ) and repeat this for every row in the table A2:AI232

Many thanks for your help & have a good weekend

 

[Joe4]

Welcome to the Board!

You can do this with regular Conditional Formatting (no VBA necessary), but if you want VBA code, just turn on the Macro Recorder as you manually apply these Conditional Formatting rules, and you will get the code that you need.

I would like to highlight grey (rgbLightGrey if possible) row range A2:AI2 if value of R2 < 0 and repeat this for every row in the table range A2:AI232.

Steps:
1. Select the entire range (A2:AI232)
2. Go to Conditional Formatting
3. Choose the "Use a formula to determine which cells to format" option
4. Enter this formula: =$R2<0
5. Choose your grey highlighting
6. Click OK

I'd like to delete content of cell (text & formulas) in range T2:AI2 if the conditional formatting is true and the cells are grey (or if the R2 < 0 ) and repeat this for every row in the table A2:AI232

You would use VBA to do this, based on the condition (R2<0), not Conditional Formatting. You can use a loop to do this.

If you put it all together, the code might look something like this:

Sub MyMacro()

    Dim rw As Long

    Application.ScreenUpdating = False

'   Conditional Formatting
    With Range("A2:AI232")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$R2<0"
        .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    
'   Loop throw rows
    For rw = 2 To 232
        If Cells(rw, "R") < 0 Then
            Range(Cells(rw, "T"), Cells(rw, "AI")).ClearContents
        End If
    Next rw
    
    Application.ScreenUpdating = True
    
End Sub