Colour cells in columns i & u to ae, based on c & i

[outlook]

Hi,

I wonder if someone can help me as I'm tearing my hair out ...

I need to colour the cells in columns I, and U to AE. The colour depends on what text is found in C and I.

For example ...

If C6 = "LAND" and I6 = "Intro", I6 & V6 needs to be coloured brown.

Can this be done, or am I just asking too much? I've tried conditional formatting but it doesn't allow enough varients.

Thank you.

 

[delaneyjm]

Someone else may come up with a better way of doing this, but this is my go at what you are looking for. You will need to post this code into the sheet module you have the data you wish to format.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Dim MyRange2 As Range
    Dim x As Long
    
    Set MyRange = Range("C2:C1000")
    
    For x = 1 To MyRange.Count + 1 Step 1
        If Range("C" & x).Value = "LAND" Then
            If Range("I" & x).Value = "Intro" Then
                Range("C" & x).Interior.Color = RGB(222, 184, 135)
                Range("I" & x).Interior.Color = RGB(222, 184, 135)
            End If
            If Range("I" & x).Value = "Homelet Charge" Then
                Range("C" & x).Interior.Color = RGB(255, 0, 0)
                Range("I" & x).Interior.Color = RGB(255, 0, 0)
            End If
            If Range("I" & x).Value = "Management" Or Range("I" & x).Value = "Monthly Fee" Then
                Range("C" & x).Interior.Color = RGB(255, 165, 0)
                Range("I" & x).Interior.Color = RGB(255, 165, 0)
            End If
            If Range("I" & x).Value = "Continuation" Then
                Range("C" & x).Interior.Color = RGB(255, 255, 0)
                Range("I" & x).Interior.Color = RGB(255, 255, 0)
            End If
            If Range("I" & x).Value = "" Then
                Range("C" & x).Interior.Color = RGB(144, 238, 144)
                Range("I" & x).Interior.Color = RGB(144, 238, 144)
            End If
        End If
        If Range("C" & x).Value = "TENC" Then
            If Range("I" & x).Value = "Admin" Or Range("I" & x).Value = "Contract" Then
                Range("C" & x).Interior.Color = RGB(0, 100, 0)
                Range("I" & x).Interior.Color = RGB(0, 100, 0)
            End If
            If Range("I" & x).Value = "Continuation" Or Range("I" & x).Value = "Card Handling" Then
                Range("C" & x).Interior.Color = RGB(173, 216, 230)
                Range("I" & x).Interior.Color = RGB(173, 216, 230)
            End If
            If Range("I" & x).Value = "Commission" Or Range("I" & x).Value = "Hallmark" Then
                Range("C" & x).Interior.Color = RGB(65, 105, 225)
                Range("I" & x).Interior.Color = RGB(65, 105, 225)
            End If
        End If
        If Range("I" & x).Value = "Refund" Then
            Range("I" & x).Interior.Color = RGB(255, 192, 203)
            Range("C" & x).Interior.Pattern = xlNone
        ElseIf Range("I" & x).Value <> "" And _
                Range("I" & x).Value <> "Intro" And _
                Range("I" & x).Value <> "Homelet Charge" And _
                Range("I" & x).Value <> "Management" And _
                Range("I" & x).Value <> "Monthly Fee" And _
                Range("I" & x).Value <> "Continuation" And _
                Range("I" & x).Value <> "Admin" And _
                Range("I" & x).Value <> "Contract" And _
                Range("I" & x).Value <> "Card Handling" And _
                Range("I" & x).Value <> "Commission" And _
                Range("I" & x).Value <> "Hallmark" Then
            Range("I" & x).Interior.Color = RGB(0, 0, 128)
        End If
    Next
End Sub