Look for each cells on the left of the range

Question

So I'm trying to make my macro look for each cells on the left of the range columns, that if it isn't empty and is expired/late (See the formula1 days), it will turn red.

Right now, my code isn't optimized but I'm new to VBA coding. I've written it with the logic above.

    Sub Macro1()
'
' Macro1 Macro
' This macro is to highlight the cell if it has been more than 5 days
'
' Keyboard Shortcut: Ctrl+b
'

' this is the range of cells
Range("L4:L1000").Select

' this targets any cell value within that range that is above the Formula1 variable which can be adjusted
    For Each cell In Range("L4:L1000")
        If Not IsEmpty(Range("L4:L1000").Offset(0, -1).Value) Then

            Selection.FormatConditions.Delete
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="6"

' this selects which colour you want to highlight the targetted cells to
            Selection.FormatConditions(1).Interior.ColorIndex = 3

            ActiveWindow.SmallScroll Down:=0

        End If
    Next

Range("N4:N1000").Select

    For Each cell In Range("N4:N1000")
        If Not IsEmpty(Range("N4:N1000").Offset(0, -1).Value) Then

            Selection.FormatConditions.Delete
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="5"

            Selection.FormatConditions(1).Interior.ColorIndex = 3

            ActiveWindow.SmallScroll Down:=0

        End If
    Next

Range("Q4:Q1000").Select

    For Each cell In Range("Q4:Q1000")
        If Not IsEmpty(Range("Q4:Q1000").Offset(0, -1).Value) Then

            Selection.FormatConditions.Delete
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="4"

            Selection.FormatConditions(1).Interior.ColorIndex = 3

            ActiveWindow.SmallScroll Down:=0

        End If
    Next

Range("S4:S1000").Select

    For Each cell In Range("S4:S1000")
        If Not IsEmpty(Range("S4:S1000").Offset(0, -1).Value) Then

            Selection.FormatConditions.Delete
            Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="2"

            Selection.FormatConditions(1).Interior.ColorIndex = 3

            ActiveWindow.SmallScroll Down:=0

        End If
    Next

End Sub

Now my problem is that it makes all the blank cells in that column red and if one cell is late, it makes next range cells (in that row) red, while I'd only want the first late one to turn red.

Answer 1

Conditional formatting works a little different from most of VBA cell programming.

You can apply a conditional format to a large swath of cells in one go.

Here's an example of the first part of your code rewritten. Working with the Range("L4:L1000"):

Sub testing()
    With Range("L4:L1000")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=AND(NOT(ISBLANK(K4)), L4>5)"
        .FormatConditions(1).Interior.ColorIndex = 3
    End With End Sub

Notice that the conditional formatting formula is written as if it were only for the first cell. Cell L4. Conditional formulas are "dragged" to apply in Excel.

You can modify this to apply the other conditional formulas in your sheet by changing the range and the formula etc.

Answer 2

Am I correct in assuming that if the cell to the left is blank, then you want it colored red?

If so, I would use this (assume, for example the column that needs coloring is column B):

dim n as integer
Range("B2").select
n= range(selection,selection.end(xldown)).count

For i=1 to n
If isblank(cells(i,1)) then
     cell(i,2).select
     Selection.FormatConditions.Delete
     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="2"
     Selection.FormatConditions(1).Interior.ColorIndex = 3
Endif
next i

Please let me know if I am misunderstanding. I can't comment to clarify without more reputation points.