Error in code. Application defined error in VBA

Samtheham 0
Viewed 30

Hi I am a total noob with VBA and am trying out a code that compare values from a range (eg. column D) with values in a range beside (eg. column C), which highlights the columns that has a different value in red, after which it moves right to compare the next range of values beside (I.e. Column e) with Column D and so on and so forth until it reaches a blank range of columns.

So far these are my codes but I’m pretty sure it’s incorrect, can someone help me please.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    Do Until IsEmpty(ActiveCell.Offset(, 1))
        Selection.FormatConditions.Add Type:=xlExpression, Formulal:=ActiveCell.Select <> ActiveCell.Offset(0, -1).Value
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0

    End With
    Loop
    ActiveCell.Offset(0, 1).Select
End Sub
vba excel
1 Answers

Try this - you need to construct a formula for the CF rule, using the Address() property of the first cell in the two ranges being compared. When you apply the rule the formula will adjust for each row, so no need to go cell-by-cell.

Sub Macro1()
    Dim rngCF As Range, addr1, addr2, cf As FormatCondition
    
    Set rngCF = Range(ActiveCell, ActiveCell.End(xlDown))
    addr1 = rngCF.Cells(1).Address(False, False)
    addr2 = rngCF.Offset(0, -1).Cells(1).Address(False, False) 'one column to left
    
    Set cf = rngCF.FormatConditions.Add(Type:=xlExpression, _
                        Formula1:="=" & addr1 & "<>" & addr2)
    With cf
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
    End With
End Sub
useryB020
Related