Loop through code and then run if statement

[123excel]

Hi, I am trying to loop through all cells in the range J10:J25. When a cell is smaller than the value in cell "D12", I want it to change color to red.

This is probably simple, but I am unsure how I can target each cell to check for the if function. ActiveCell does not seem to work.

I could not manage to find the answear on google, so I am very thankful for your help!

Here is the code I have come up with so far:

Sheets("Data").Select
Range("D12").Select


Dim rng As Range, cell As Range
Set rng = Range("J10:J25")
For Each cell In rng



If ActiveCell < Range("D12") Then


ActiveCell.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    End If


Next cell


End Sub

 

[Special-K99]

Am no VBA expert but try changing both references from "Activecell" to "cell"

 

[Joe4]

Since you are looping through the range, and setting each cell equal to "cell", replace "ActiveCell" with cell, and do not use Select, i.e.

Sheets("Data").Select

Dim rng As Range, cell As Range
Set rng = Range("J10:J25")
For Each cell In rng

    If cell < Range("D12") Then
         With cell.Interior
             .Pattern = xlSolid
             .PatternColorIndex = xlAutomatic
             .Color = 255
             .TintAndShade = 0
             .PatternTintAndShade = 0
         End With
    End If

Next cell

End Sub

 

[123excel]

Thanks a lot! It worked and I learn something along the way

For some reason I get a error message after the code has run.. saying error nr 13: "Incompatible types". The highlighted part is: "If cell < Range("D12") Then".

The code still works but any idea why this error occurs?

 

[Fluff]

What is the value of "cell" when the error occurs?
Do you have any errors in J10:J25?

 

[Joe4]

This code will tell you what cell is causing the error:

Sub MyMacro()

    Dim rng As Range, cell As Range

    Sheets("Data").Select
    Set rng = Range("J10:J25")
    
    On Error GoTo err_chk
    For Each cell In rng
        If cell < Range("D12") Then
             With cell.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next cell
    On Error GoTo 0
    
    Exit Sub
    

err_chk:
    MsgBox "Error in cell " & cell.Address(0, 0)

End Sub

Take a look at that cell and its contents.

 

[123excel]

Thank you so much for your superb help! There was a problem in the range (a cell had a #div error. Thank you for taking the time!

 

[DanteAmor]

Thank you so much for your superb help! There was a problem in the range (a cell had a #div error. Thank you for taking the time!

In the following way you can evaluate if the cell has an error

    Sheets("Data").Select
    Dim cell As Range
    For Each cell In Range("J10:J25")
[COLOR=#0000ff]        If Not IsError(cell) Then[/COLOR]
            If cell < Range("D12") Then
                 With cell.Interior
                     .Pattern = xlSolid
                     .PatternColorIndex = xlAutomatic
                     .Color = 255
                     .TintAndShade = 0
                     .PatternTintAndShade = 0
                 End With
            End If
        End If
    Next cell

 

[Joe4]

You are welcome.
Glad we were able to help.