# Conditional code to add Background color to cells



## mikemcbain (Dec 28, 2022)

Happy New Year Magicians,

I would like some code for a macro that I can Run from a button and which will check Column V and add background colour .Color = 65535 to each Cell that contains a Value greater than 999.

And seperate code for another spreadsheet which will check Column BM and if any Cells contain values less than 5.600 then it will add background colour .Color = 65535 to each Cell on those same Rows but in Column K.

Hoping everyone has a very prosperous 2023

Old Mike.


----------



## HongRu (Dec 28, 2022)

How about conditional formatting ?
WB1KVBM11000629995.43500420005.556SH1Cells with Conditional FormattingCellConditionCell FormatStop If TrueK:KExpression=AND(BM1<5.6,BM1<>"")textNOV:VExpression=V1>999textNO


----------



## mikemcbain (Dec 28, 2022)

HongRu said:


> How about conditional formatting ?
> WB1KVBM11000629995.43500420005.556SH1Cells with Conditional FormattingCellConditionCell FormatStop If TrueK:KExpression=AND(BM1<5.6,BM1<>"")textNOV:VExpression=V1>999textNO


Aaahh HongRu that will do very nicely thank you. Mike.


----------



## HongRu (Dec 28, 2022)

Glad to help.


----------



## kevin9999 (Dec 28, 2022)

mikemcbain said:


> I would like some code for a macro that I can Run


You have a perfectly good solution from @HongRu, but just in case you _were_ interested in using code to add the formatting, try the following:

```
Option Explicit
Sub Add_Color_Formatting()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")  '<~~ change to actual sheet name
    Set ws2 = Worksheets("Sheet2")  '<~~ change to actual sheet name
    
    Dim Rng As Range
    Set Rng = ws1.Range("V2", ws1.Cells(Rows.Count, "V").End(xlUp))
    With Rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="999"
        .FormatConditions(1).Interior.Color = vbYellow
    End With
    
    Set Rng = ws2.Range("K2:K" & ws2.Cells(Rows.Count, "BM").End(xlUp).Row)
    With Rng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
        Formula1:="=AND(BM2<5.6,BM2<>"""")"
        .FormatConditions(1).Interior.Color = vbYellow
    End With
End Sub
```


----------

