CODEWITHSUNDEEP
excelvbaconditional-formatting
Afonso Medeiros
Afonso Medeiros

Reputation: 67

Conditional formatting with vba background simple tint

The reason I need a conditional formatting on VBA is because I work with plenty of "unexperienced" coworkers and it has been a battle patronizing our sector. We use a lot copy and paste between several sheets and that's the reason I don't want over-pasting many many formats.

I'd like some help with the code, please. I use Excel 365 in PT-BR.

There are 2 situations.

Situation 1

Apply to the whole column I, which is a date column (DD/MM/YYYY).

=DATA.VALOR($I1)<DATA.VALOR($B1)

and it goes for each row. Formatting - background color: Hex #D6DCE4 [some grey-blue-ish light color]

And then...

Situation 2

Apply to the whole column J, which is a accounting column.

Cell's value < 0

Formatting - background color: Hex #FF9999 [light-red color]

This help would be much appreciated. Thanks :)

Upvotes: 0

Views: 160

Answers (2)

Afonso Medeiros
Afonso Medeiros

Reputation: 67

After a while, with the help of the previous answer, I came up with this solution:

' Formatação condicional
Application.CutCopyMode = False
Cells.FormatConditions.Delete

With Columns("J:J").FormatConditions
    With .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="=0")
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10066431
            .TintAndShade = 0
        End With
    End With
End With

With Columns("I:I").FormatConditions
    With .Add(Type:=xlExpression, Formula1:="=DATA.VALOR($I1)<DATA.VALOR($B1)")
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight2
            .TintAndShade = 0.799981688894314
        End With
    End With
End With

Upvotes: 2

Amiga500
Amiga500

Reputation: 1275

I've done this, but don't have it to hand - here is a solution of the same shape from elsewhere:

Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("A2", Range("A2").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=$a$1")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")

'define the format applied for each conditional format
With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
End With

With cond2
    .Interior.Color = vbRed
    .Font.Color = vbWhite
End With

With cond3
    .Interior.Color = vbYellow
    .Font.Color = vbRed
End With

Upvotes: 0

Related Questions