VBA to highlight cells based on multiple criteria

[janu319]

Hi,

I need to highlight cells in 5 columns based on the cell values, hope this can be done with VBA.

As shown in the attached image, the “Code” should be 1, 2, 3 or 4. For all other values, those cells needs to be highlighted in Yellow Color.

If the “Code” is 1 or 2, then the “Direction”, “Min”, “Max” and “Report” should be filled. Conditions are “Direction” must be one of E/W/N/S, if it is empty or filled with other value, then those cells needs to be highlighted in Yellow Color. “Min” and “Max” must be greater than 0(zero) but not more than 4.9. “Report” should not be empty.

If the “Code” is 3 or 4 or any other value, then the “Direction”, “Min”, “Max” and “Report” should be empty, if the cells are filled with any data, those needs to be highlighted. Min and Max can be 0 (zero).

Can we filter the rows which have at least one cell highlighted in Yellow?

I tried the conditional formatting but not much success.

Hope you can help me with this.

Thanks in advance.

 

[Zot]

See if this works

Sub Test()

Dim Hide As Boolean
Dim cell As Range, rngCode As Range
Dim cellCol As Range, rngCol As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.ActiveSheet
Set rngCode = ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))

For Each cell In rngCode
    Hide = True
    Set rngCol = ws.Range("D" & cell.Row, "G" & cell.Row)
    For Each cellCol In rngCol
        Select Case cell
            Case 1, 2
                Select Case ws.Cells(1, cellCol.Column)
                    Case "Direction"
                        If Not cellCol = "E" And Not cellCol = "W" And Not cellCol = "N" And Not cellCol = "S" Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Min", "Max"
                        If cellCol > 0 And Not cellCol > 4.9 Then
                            ' Do nothing
                        Else
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Report"
                        If cellCol = "" Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                End Select
            Case Else
                If IsEmpty(cellCol) Then
                    cellCol.Interior.ColorIndex = 6
                    Hide = False
                End If
        End Select
    Next
    If Hide Then cell.EntireRow.Hidden = True
Next
    
End Sub

 

[janu319]

Thank you Zot.
For "Code" 1 and 2 it is working perfect.
For "Codes" 3, 4 and other values, it is highlighting correct cells and not highlighting wrong cells.
If the "Code" is other than 1, 2, 3 and 4, then that cells needs to be highlighted.

 

[Zot]

Thank you Zot.
For "Code" 1 and 2 it is working perfect.
For "Codes" 3, 4 and other values, it is highlighting correct cells and not highlighting wrong cells.
If the "Code" is other than 1, 2, 3 and 4, then that cells needs to be highlighted.

View attachment 48956

I could not understand what you were trying to indicate on your example above.

My understanding is that for Code 1, 2, 3, and 4, the highlight is correct. For code other than that you need the code to be highlighted too? Regardless whether the criteria are met, all cell in that row will need to be highlighted too?

 

[janu319]

Sorry for the confusion.
Below matrix shows the possible values for Direction, Min, Max and Report for each code. If the values are not like this, then I want those cells to in highlighted in Yellow color.
Also, if the Code value it self is not one of 1, 2, 3 or 4, then I want those cells to be highlighted as well.

Hope it is clear.

	Possible values
S.No	Code	Direction	Min	Max	Report
1	1	E/W/N/S	0-4.9	0-4.9	Fill
2	2	E/W/N/S	0-4.9	0-4.9	Fill
3	3	Empty	0 or Empty	0 or Empty	Empty
4	4	Empty	0 or Empty	0 or Empty	Empty
5	All other codes	Empty	0 or Empty	0 or Empty	Empty

 

[Zot]

Actually your instruction was clear in the very beginning but my brain was unclear .
Try code below. I got the result like the one in your example. Should have check carefully before.
I use Hide to hide row without any highlight like before

Sub Test()

Dim Hide As Boolean
Dim cell As Range, rngCode As Range
Dim cellCol As Range, rngCol As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.ActiveSheet
Set rngCode = ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp))

For Each cell In rngCode
    Hide = True
    Set rngCol = ws.Range("C" & cell.Row, "G" & cell.Row)
    For Each cellCol In rngCol
        Select Case cell
            Case 1, 2
                Select Case ws.Cells(1, cellCol.Column)
                    Case "Direction"
                        If Not cellCol = "E" And Not cellCol = "W" And Not cellCol = "N" And Not cellCol = "S" Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Min", "Max"
                        If cellCol > 0 And Not cellCol > 4.9 Then
                            ' Do nothing
                        Else
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Report"
                        If IsEmpty(cellCol) Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                End Select
            Case Else
                Select Case ws.Cells(1, cellCol.Column)
                    Case "Code"
                        If Not cellCol = 3 And Not cellCol = 4 Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Direction", "Report"
                        If Not cellCol = "" Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                    Case "Min", "Max"
                        If Not cellCol = 0 And Not IsEmpty(cellCol) Then
                            cellCol.Interior.ColorIndex = 6
                            Hide = False
                        End If
                End Select
        End Select
    Next
    If Hide Then cell.EntireRow.Hidden = True
Next
    
End Sub

 

[janu319]

Works perfect.
Thank you Zot.

 

[Zot]

Works perfect.
Thank you Zot.

Glad can help