Conditional Formatting VBA (Compare 2 column)

[NewYears1978]

I am adding in a column in a long macro and then I need to add conditional formatting to that column in VBA. Struggling.

I need to compare column V to lastrow to column P to lastrow and have it one color or another.

This is part of what I had so far but not sure how to define i to mean to use the current row... so I got stuck. Hellllp =D

Sub JP_AddConditions()

    With Range("V" & i).FormatConditions
            .Add Type:=xlExpression, Operator:=xlEquals, Formula1:="=$P$" & i & "<>$V$" & i
        End With
        Range("V" & i).FormatConditions(1).Interior.ColorIndex = 6
  End Sub

[NewYears1978]

I forgot to put that I used record to see how it did it and it works but it is really...hefty code. Also I have no clue where the .color values come from so I can't cahnge them easily..what are these long numbers? HEX and RGB won't work.

Sub TEST3()
'
' TEST3 Macro
'

'
    Range("V2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=V2=P2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 423523523
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=V2<>P2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 323423423
    End With
     Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=V2=""No Markup"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 2344234
        
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

[TMS]

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Dan_W]

So the way this works is:

Range("V2").Select
    Range(Selection, Selection.End(xlDown)).Select

As though you were doing it manually, this is saying that the range to be conditionally formatted will span from V2 down to the last row in that column.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=V2=P2"

It then applies the formula

=V2=P2

to each cell in the selection, so this addresses your question re: how to define I. In terms of colour, you can use the RGB function here

 .Color = RGB(255,100,100)

Hope that helps.

[NewYears1978]

Dan yes! Broken down that made so much more sense, also made me realize that I always screw up conditionally formatting (because you need that first...starting cell, whatever it is called (the =V2 part).

I got it working with

Sub JP_AddConditions()

    Range("V2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=V2=P2"
    Selection.FormatConditions(1).Interior.ColorIndex = 7

End Sub

Thank you kindly, also TMS thanks for replying even though I didn't end up having to use a sample =D

[Dan_W]

You're very welcome, and I'm glad that you understood what I was getting at with the starting cell part. I wasn't quite sure how I would explain it it if you didin't know what I was talking about. And thank you for posting the solution - it will help others in a similar situation, I'm sure.

What I did do was explain the Long numbers. That's the colour code that Excel understand and it covers the full RGB spectrum. What you've used in your solution above is the ColorIndex property - that has a spectrum of (I think) 56 colours and I believe it's a legacy feature from an older Excel version. But the best solution is the one that works! Thanks for letting us know.