Format color cell when date is higher then specific field

[jmsla...@gmail.com]

Dear,

I know that coloring cell based on another cell can be done by Conditional Formatting, but because of several reasons I want to do that with a macro.

For example (with this example I can setup more I needed);

In column A are in several rows date values registered (dd/mm/yyyy hr:mm).
For each cell in column A;
If the date > Cell $B$1 then the cell fill color should be yellow.
If the cell is empty then cell fill color should be green.

regards, Johan

[Claus Busch]

Hi Johan,

try:

Sub Color()
Dim lRow As Long
Dim rngC As Range

With ActiveSheet
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A2:A" & lRow)
If rngC > .Range("B1") Then
rngC.Interior.Color = vbYellow
ElseIf rngC = "" Then
rngC.Interior.Color = vbGreen
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Microsoft 365 for business

[jmsla...@gmail.com]

THANKS !!!!
Regards, Johan