Apply "hard" conditional formatting using VBA

[SueBK]

I want to apply 'conditional' formatting to a worksheet, but I'm not sure how to get started. I would rather 'hard' format the cells, than actually use the conditional formatting.

Here's the basics of what I want to achieve:

1. If 'This week'!J2 <> 'Last week'!J2 - fill 'This week'!J2 with red and cancel the procedure. J2 is my record ID field; so if J2 doesn't match between the sheets there's no point proceeding, unless there's some way of matching Column J regardless of where the match occurs. That is, if I can code so it says, "Match 'This week'!J2, with 'Last week'!J-whatever", and then run the comparisons on the 'whatever' row.
ELSE
2. If 'This week'!W2 > 'Last week'!W2 - fill 'This week'!W2 with orange
3. If 'This week'!X2 <> 'Last week'!X2 AND <'Last week'!W2 - fill 'This week'!X2 with green
4, 5, 6. More conditions on other columns

Repeat for Row 3 through to the end

 

[pbornemeier]

This code will do as you asked:

Option Explicit

Sub ConditionallyFormat()

    Dim lThisWeekLastRow As Long
    Dim lX As Long
    Dim oFound As Object
    Dim vSearchFor As Variant
    Dim lFoundRow As Long
    Dim lNoMatchCount As Long
    
    With Worksheets("This Week")
    
        'Uncomment next row to clear all colors on 'This Week' worksheet before procedure is run
        '.Cells.Interior.Color = -4142
    
        lThisWeekLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        For lX = 2 To lThisWeekLastRow  'Change 2 to 1 if there are no headers
            
            vSearchFor = .Cells(lX, "J").Value
            
            'Find Match in Last Week
            Set oFound = Worksheets("Last Week").Columns("J:J").Find(What:=vSearchFor, _
                LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                
            If Not oFound Is Nothing Then
                'Found a match in column J
                lFoundRow = oFound.Row  'this is the whatever row
                
                'Condition 2
                If .Cells(lX, "W").Value > Worksheets("Last Week").Cells(lFoundRow, "W").Value Then
                     .Cells(lX, "W").Interior.Color = 49407 'Orange
                End If
                'Condition 3
                If .Cells(lX, "X").Value <> Worksheets("Last Week").Cells(lFoundRow, "X").Value _
                    And .Cells(lX, "X").Value < Worksheets("Last Week").Cells(lFoundRow, "W").Value Then
                     .Cells(lX, "X").Interior.Color = 5287936 'Green
                End If
                'Condition 4,5,6
                    'Not enough information provided
                
                
            Else
                'No Match
                .Cells(lX, "J").Interior.Color = 255    'Red
                lNoMatchCount = lNoMatchCount + 1
            End If
            
        Next
        
    End With
            
    If lNoMatchCount > 0 Then
        MsgBox lNoMatchCount & " of this week column J cells had no match in Last Week column J."
    End If
        
End Sub

 

[SueBK]

Thank you. I found code while searching for something else last week, but appreciate you responding

I've gone from never having written an Excel macro to this sort of stuff in four weeks. It's a bit of a head spin.