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

 

[footoo]

Could be done with conditional formatting :

=(E11="")*COUNTA(B11:C11,D11,F11)

 

[footoo]

If you need a macro :

Sub v()
[E11].Interior.ColorIndex = xlNone
If [E11] = "" And WorksheetFunction.CountA([B11:C11], [D11], [F11]) = 4 Then _
    [E11].Interior.ColorIndex = 3
End Sub

 

[bebo021999]

CF formula:

=AND(E11="",COUNTA(B11:F11)=4)

or VBA code:

Range("E11").Interior.Color = xlNone
If WorksheetFunction.CountA(Range("B11:F11")) = 4 And IsEmpty(Range("E11")) Then Range("E11").Interior.Color = vbRed

 

[footoo]

Could be done with conditional formatting :

=(E11="")*COUNTA(B11:C11,D11,F11)

Revision to post #2 :

=(E11="")*(COUNTA(B11:C11,D11,F11)=4)

 

[Zot]

Try this. Put under worksheet module for event trigger

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngTrig As Range

Set rngTrig = Union(Range("B11,D11"), Range("F11"))
If Not Intersect(Target, rngTrig) Is Nothing Then
    If Application.WorksheetFunction.CountA(rngTrig) > 0 Then
        Range("E11").Interior.ColorIndex = 3
    Else
        Range("E11").Interior.ColorIndex = xlNone
    End If
End If

End Sub

 

[bebo021999]

@Zot

1) E11 could be changed too. should be

Set rngTrig = Range("B11:F11")

2) B to F11 could be cells with formula. Thus worksheet_Change could not fire it.

 

[Zot]

@Zot

1) E11 could be changed too. should be

Set rngTrig = Range("B11:F11")

2) B to F11 could be cells with formula. Thus worksheet_Change could not fire it.

We need more detail from OP

 

[Pinaceous]

Hello Gents'

The code is working well but I would like it to produce the interior color red cell for E11 only IF B11 & C11 & D11 and F11 have contents.

Based on this criteria, I am attaching a scenario to see if this can be produced.

Many thanks for providing your codes and input!
Pinaceous

 

[Zot]

My understanding now is that when all B, C ,D and F not blank, not if any one is not blank.

I see that you want to implement on any row instead of just row 11. The code will count 0 is a content, not blank and will work on any row on the sheet for the same columns.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long
Dim rngTrig As Range

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

End Sub