Color conditional in vba for columns

**alv_jog** · 09-10-2024, 12:13 PM

Hello,

I'm trying to make a macro for each week column.

Column A are the unique codes and for each code there are two data of sales and fcst

cond. excel.xlsx cond. excel.xlsx
with the following conditional:

1. If we do not have Sales but we do have Fcst, both are red > If in the Sales cell = 0 and Fcst if it has data > Red
2. If we have Sales but it is below Fcst it turns both orange > Yes in the Sales cell > Fcst cell > Orange
3. If we have Sales and it is the same quantity as the Fcst, they both turn green
4. If we have Sales and the Fcst is less than this or is 0, both are turned purple.

Thank you.

**JohnTopley** · 09-10-2024, 02:36 PM

Option Explicit

Sub demo()
Dim rng
Dim lc As Long, lr As Long, icol As Long, irow As Long

Application.ScreenUpdating = False

lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, "A").End(xlUp).Row
Range("D2:F" & lr).Interior.Pattern = xlNone
For icol = 4 To lc
    For irow = 2 To lr - 1 Step 2
        If Not IsEmpty(Cells(irow, icol)) Then
            If Cells(irow + 1, icol) = 0 Then
               Cells(irow, icol).Resize(2, 1).Interior.Color = RGB(240, 100, 100)
            Else
                If Cells(irow, icol) > Cells(irow + 1, icol) Then
                    Cells(irow, icol).Resize(2, 1).Interior.Color = RGB(255, 180, 100)
                Else
                    If Cells(irow, icol) < Cells(irow + 1, icol) Then
                        Cells(irow, icol).Resize(2, 1).Interior.Color = RGB(240, 80, 170)
                    Else
                        If Cells(irow, icol) = Cells(irow + 1, icol) Then Cells(irow, icol).Resize(2, 1).Interior.Color = RGB(60, 180, 70)
                    End If
                End If
            End If
        End If
    Next irow
Next icol

Application.ScreenUpdating = True

End Sub

results in Sheet2: note there are no GREEN as no exact matches!