VBA Red Cell Criteria

[Pinaceous]

Good Day All,

In working with line 11 of my sheet, where I would like cell E11 to produce a highlighted red cell only if it is empty AND ONLY if the cells of that row specifically cells B11, C11, D11 & F11 are populated.

Likewise, if all of the cells of this row (row 11) of B11, C11, D11 & F11 are not populated, then cell E11 will not produce a highlighted red cell.

All of these cells have to be populated in order for the cell E11 to be highlighted, where if all of them are not populated then the code will not produce a highlighted red cell for E11.


Can you please help me create a vba code around this condition?

Thank you!
Pinaceous

 

[Zot]

Hi Zot!

Your code works great but I would like for you to please add an additional criteria to your code, if possible.

Do you know if you can add this criteria to your sub; whereby, if the cell has an interior color of red CELL in Column E that a text value of "ABC123" will return the inside of the cell?



Please see picture attached.

Thank you!
pinaceous

View attachment 72146

Since it is a Change event triggered, the value change in E triggers the event for the 2nd time thus resulting unexpected outcome. SO, need to disable the trigger temporarily.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long
Dim rngTrig As Range

n = Target.Row
Set rngTrig = Range("B" & n, "F" & n)
Application.EnableEvents = False
If Not Intersect(Target, rngTrig) Is Nothing Then
    If Application.WorksheetFunction.CountA(rngTrig) = 4 And Range("E" & n) = "" Then
        Range("E" & n).Interior.ColorIndex = 3
        Range("E" & n) = "ABC123"
    Else
        Range("E" & n).Interior.ColorIndex = xlNone
        Range("E" & n) = ""
    End If
End If
Application.EnableEvents = True

End Sub

 

[Pinaceous]

Since it is a Change event triggered, the value change in E triggers the event for the 2nd time thus resulting unexpected outcome. SO, need to disable the trigger temporarily.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long
Dim rngTrig As Range

n = Target.Row
Set rngTrig = Range("B" & n, "F" & n)
Application.EnableEvents = False
If Not Intersect(Target, rngTrig) Is Nothing Then
    If Application.WorksheetFunction.CountA(rngTrig) = 4 And Range("E" & n) = "" Then
        Range("E" & n).Interior.ColorIndex = 3
        Range("E" & n) = "ABC123"
    Else
        Range("E" & n).Interior.ColorIndex = xlNone
        Range("E" & n) = ""
    End If
End If
Application.EnableEvents = True

End Sub

Hey Zot!

This is great! I like it! Now its coming together!

I observe in your code that if you want to override the value of "ABC123" with another value, everything disappears in that cell; both the interior color and both the old value ("ABC123") and any new proposed entered value.

For example, if I enter "truth" in trying to replace the "ABC123" everything disappears as in the Picture#3.

Do you know, if you can possibly provide to your existing code that if another value (a new value) is entered into the interior red color cell that it will remove the interior color red cell and keep that new text? For example, as in Picture#4?


Picture#1.
For example: This is the code produced as expected.

Picture#2.
If I wanted to override the "ABC123" with the word "truth" as the new value.
(This is happening before I press enter.)

Picture#3.
This is what appears after I press enter with that or any new value.

Picture#4.
Do you know if you can adjust your code, so that when a new value overrides the "ABC123", for example, "truth" that this appears?

This would really help a bunch!

Thanks again!
pinaceous

PS. Great coding!!

 

[Zot]

The conditions get twisted and it is twisting my mind . There are many possibilities that might have not been mentioned yet

In this code:
If B, C, D, F present, then if value E is deleted, if will come back to RED ABC123 to satisfy the condition set before
If overwrite E with any value, no more highlighted RED and it can be any value except ABC123 which will make the cell RED again.

Perhaps this can be simplified but still short and fast. Why bother?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long
Dim rngTrig As Range

n = Target.Row
Application.EnableEvents = False
Set rngTrig = Union(Range("B" & n, "D" & n), Range("F" & n))
If Not Intersect(Target, rngTrig) Is Nothing Then
    If Application.WorksheetFunction.CountA(rngTrig) = 4 Then
        If Range("E" & n) = "" Then
            Range("E" & n).Interior.ColorIndex = 3
            Range("E" & n) = "ABC123"
        Else
            Range("E" & n).Interior.ColorIndex = xlNone
        End If
    Else
        Range("E" & n).Interior.ColorIndex = xlNone
        Range("E" & n) = ""
    End If
End If
If Not Intersect(Target, Range("E" & n)) Is Nothing Then
    If Range("E" & n) = "" Then
        Range("E" & n).Interior.ColorIndex = 3
        Range("E" & n) = "ABC123"
    Else
        If Not Range("E" & n) = "ABC123" Then
            Range("E" & n).Interior.ColorIndex = xlNone
        Else
            Range("E" & n).Interior.ColorIndex = 3
        End If
    End If
End If
Application.EnableEvents = True

End Sub

 

[Pinaceous]

The conditions get twisted and it is twisting my mind . There are many possibilities that might have not been mentioned yet

In this code:
If B, C, D, F present, then if value E is deleted, if will come back to RED ABC123 to satisfy the condition set before
If overwrite E with any value, no more highlighted RED and it can be any value except ABC123 which will make the cell RED again.

Perhaps this can be simplified but still short and fast. Why bother?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long
Dim rngTrig As Range

n = Target.Row
Application.EnableEvents = False
Set rngTrig = Union(Range("B" & n, "D" & n), Range("F" & n))
If Not Intersect(Target, rngTrig) Is Nothing Then
    If Application.WorksheetFunction.CountA(rngTrig) = 4 Then
        If Range("E" & n) = "" Then
            Range("E" & n).Interior.ColorIndex = 3
            Range("E" & n) = "ABC123"
        Else
            Range("E" & n).Interior.ColorIndex = xlNone
        End If
    Else
        Range("E" & n).Interior.ColorIndex = xlNone
        Range("E" & n) = ""
    End If
End If
If Not Intersect(Target, Range("E" & n)) Is Nothing Then
    If Range("E" & n) = "" Then
        Range("E" & n).Interior.ColorIndex = 3
        Range("E" & n) = "ABC123"
    Else
        If Not Range("E" & n) = "ABC123" Then
            Range("E" & n).Interior.ColorIndex = xlNone
        Else
            Range("E" & n).Interior.ColorIndex = 3
        End If
    End If
End If
Application.EnableEvents = True

End Sub

Zot!

That is so wonderful!

Thank you so much!
pinaceous

 

[Pinaceous]

Set rngTrig = Union(Range("B" & n, "D" & n), Range("F" & n))

Hi Zot,

The code works good. Could I ask you what this line of your code does, just for my education?

Thanks,
pinaceous

 

[Zot]

Hi Zot,

The code works good. Could I ask you what this line of your code does, just for my education?

Thanks,
pinaceous

The Union is used to group non-contiguous range together. In this case I grouped Range(Bx:Dx) and Range(Fx) and assigned range name as rngTrig, not to include Range(Ex)