VBA Conditional Highlighting

[Barklie]

Hi,

I have a large data set (Range A7:AD7445) to which I want to apply conditional formatting. For each row, if the contents of Column A, Column U, and Column AD are the same; then the contents of Column W should also be the same. If the contents of the three columns are the same but the data in Column W is not, then I would like to highlight either the whole row or just one cell in that row. I am racking my brain but can't think of how I could do this. Any ideas would be greatly appreciated.

Thanks,
Barklie

 

[JackDanIce]

Try:

Sub Highlight_v1()

Dim i as Long

Application.ScreenUpdating = False

For i = 7 To 7445
    If Range("A" & i) = Range("U" & i) And Range("A" & i) = Range("AD" & i) Then
        If Range("A" & i) <> Range("W" & i) Then Range("A" & i & ":AD" & i).Interior.ColorIndex = 5
    End If
Next i

Application.ScreenUpdating = True

End Sub

You can look up different values, Interior.ColorIndex to change the colour

 

[Barklie]

Thank you for the quick response. However, I now realize I didn't specify my question correctly. What's going on is I am looking for pricing discrepancies (Column W). Column A is the billing period (quarterly or monthly), Column U is the the 4 digit product code, and Column AD is the region. Essentially if the same product is sold within the same region with the same billing date, the price should be the same. So the columns shouldn't be equal to each other (like I incorrectly said) but rather equal to other rows with the same information. Sorry for the confusion.

 

[JackDanIce]

If you gave an actual example, that would make it much easier to understand your problem.. Difficult to otherwise know what your spreadsheet looks like or the setup is

 

[Barklie]

I figured it out. I sorted the entire table and then checked each row with the next row and the previous row. Whenever all the conditions matched up except the price, I highlighted it.

Sub Find_Price_Discrepancy()

' Macro5 Macro
' Keyboard Shortcut: Ctrl+q

'Applies a 3-level Sort to the Table
    Application.ActiveSheet.Sort.SortFields.Clear
    Application.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "A7:A7445"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    Application.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "U7:U7445"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    Application.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "AD7:AD7445"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Application.ActiveSheet.Sort
        .SetRange Range("A6:AD7445")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Finds Rows containing the same Billing Frequency, Product Code, and Region Code but different Standard Price
Dim i As Long

Application.ScreenUpdating = False

For i = 7 To 9999
    If Range("A" & i) = Range("A" & i + 1) And Range("U" & i) = Range("U" & i + 1) And Range("AD" & i) = Range("AD" & i + 1) And Range("W" & i) <> Range("W" & i + 1) Then
     Range("A" & i & ":AD" & i).Interior.ColorIndex = 8
    End If
    If Range("A" & i) = Range("A" & i - 1) And Range("U" & i) = Range("U" & i - 1) And Range("AD" & i) = Range("AD" & i - 1) And Range("W" & i) <> Range("W" & i - 1) Then
     Range("A" & i & ":AD" & i).Interior.ColorIndex = 8
     
    End If
Next i

Application.ScreenUpdating = True

End Sub

Thanks!

 

[JackDanIce]

Glad you found a solution