How to color column if certain conditions match-CodePudding

I want to implement some logic into my scheduler but I don't really have the know how on how to check for values in a column. What I would like to implement, is to check if the values "F" and "S" exists at least once in a day, otherwise the column should be colored. Furthermore I would like to check if the values "U", "SCH" and "ZA" do not occur more than 3 times in a given day, otherewise the column should again be colored.

Here is an example if the discription given isn't enough:

Legend:

F = early Shift,
S = late Shift,
U = Vacation,
ZA = compensatory time,
SCH = training

CodePudding user response：

The idea of the code is to set the range manually - Range("A1:P20").
Then with a nested loop, it checks the values of each cell in the range, if it is part of the Array("U", "SCH", "ZA, "F", "S").
If it is, the column of the cell is highlighted in yellow with this part of the code - myRange.Columns(myCell.Column).Interior.Color = vbYellow

Sub ColorMe()

    Dim myRange As Range
    Set myRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:P20")
    Dim myCell As Range
    
    Dim myArray As Variant
    myArray = Array("U", "SCH", "ZA", "F", "S")
    Dim myItem As Variant
    
    For Each myCell In myRange.Cells
        For Each myItem In myArray
            If myItem = UCase(Trim(myCell)) Then
                myRange.Columns(myCell.Column).Interior.Color = vbYellow
            End If
        Next
    Next myCell

End Sub

Using the points above and your business logic, you may introduce a variable, deciding whether the column of the range should be colored or not.

CodePudding user response：

As Luuk sugested try this formula in conditional formating (adjust it by yourself):

=OR(OR(COUNTIF(B:B,"F")<1,COUNTIF(B:B,"S")<1),(COUNTIF(B:B,"U") COUNTIF(B:B,"SCH") COUNTIF(B:B,"ZA"))>2)

Result should look like this: