Conditional code to add Background color to cells

[mikemcbain]

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]

How about conditional formatting ?

WB1
	K											V																																											BM
1												1000																																											6
2												999																																											5.4
3												500
4												2000																																											5.5
5																																																							6
SH1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K:K	Expression	=AND(BM1<5.6,BM1<>"")	text	NO
V:V	Expression	=V1>999	text	NO

 

[mikemcbain]

How about conditional formatting ?

WB1
	K											V																																											BM
1												1000																																											6
2												999																																											5.4
3												500
4												2000																																											5.5
5																																																							6
SH1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K:K	Expression	=AND(BM1<5.6,BM1<>"")	text	NO
V:V	Expression	=V1>999	text	NO

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

 

[HongRu]

Glad to help.

 

[kevin9999]

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