Filtering for Multiple Colors in One Column in Excel-CodePudding

I've been having issues conditionally formatting a sheet 2 (Shipping Request List) that is mirroring select columns of sheet 1 (Master List).

-Starting in sheet 2 (Shipping Request List), column B, cell B2, has ='Master List'!D2 (from sheet 1) and continues down for hundreds of rows.

-The data in this field is mirroring cell 'Master List'!D2 (sheet 1) which could either be "Open" (or a number of other items based off a drop down list... no formulas). I'm only interested in what happens when the cell says, "Open".

-Cell D2 in sheet 1 has the following conditional formats based on D2 saying "Open" and a date in cell V2 in the Master List sheet.

=AND($V2<TODAY(),$D2="open") [turns purple]
=AND($V2-TODAY()>=0, $V2-TODAY()<=2,$D2="open") [turns red]
=AND($V2-TODAY()>=3, $V2-TODAY()<=4,$D2="open") [turns orange]
=AND($V2-TODAY()>=5, $V2-TODAY()<=7,$D2="open") [turns yellow]

When I try conditional formatting based on the values in sheet 2, i.e. by the value "Open" mirrored from sheet 1, the colors are not correct.

I tried various approaches.

Approach 1: Change the conditional format equations in sheet 2 to reference the cell in sheet 1 instead of directly referencing the cells in sheet 2.

Approach 2: Try using INDIRECT in the conditional format equations in sheet 2...but I'm not sure I did this correctly.

Approach 3: Use a VBA code to create a UDF on sheet 1 to extract the color code of column D into an adjacent column (C), then filter by that number for the codes that represent purple, red, orange, and yellow. (I am not at all good with VBA, but I can copy and paste a module and follow instructions.)

Function FindColor(n As Range) As Integer
FindColor = n.Interior.ColorIndex
End Function

In column C, C2 had =findcolor(D2) and then that got pulled down. This approach gave me numbers, but it gave me the same number for purple, blank, etc... extremely inconsistent.

Approach 4: In new column C, add a formula to make the words either "purple", "red", "orange", or "yellow" appear based on the conditions mentioned in the conditional formatting formulas. I got only so far as the formula started getting a little too complicated for me to get through it. Only got this far (didn't even add orange and yellow yet) but the formula below is not returning "red" when the conditions call for it:

=IF(AND($V662<TODAY(),$D662="open"),"purple",IF(AND($V662-TODAY()>=0,$V662-TODAY()<=2,$V662="open"),"red",""))

I think I was getting close on the last approach, but I realize it's a lot of formula and will be added to 700 rows, which will continue to grow.

CodePudding user response：

As a slightly different approach: you could use a UDF to return the "color" for each row, and then run your conditional formatting rules based on the return values.

Put this in a regular module:

Function Classify(theDate, theStatus) As String
    If theStatus = "open" Then   'check status
        If Len(theDate) > 0 Then   'has a date?
            Select Case theDate - Date
                Case Is < 0: Classify = "purple"
                Case Is >= 5: Classify = "yellow"
                Case Is >= 3: Classify = "orange"
                Case Is >= 0: Classify = "red"
            End Select
        End If
    End If
End Function

In your worksheet you'd use (eg)

=Classify($V662, $D662)