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

 

[Pinaceous]

Zot!

You are very perceptive! Thank you for your code. It works excellent!

Do you know if the interior color can disappear when an entry is entered into it?

Thanks so much for that code!

-pin

 

[Zot]

Zot!

You are very perceptive! Thank you for your code. It works excellent!

Do you know if the interior color can disappear when an entry is entered into it?

Thanks so much for that code!

-pin

You can try. I did not put any condition related to column E. So, the color should remain.

 

[Pinaceous]

Hi Zot,

I would like for you to possibly add a condition related to column E to your code.

Where the interior color of red for an entry upon column E will disappear, if the interior color of red has an entered value there.

For example in the image, I entered in the value of "ddd" for column E.

So, now after this entry, I would like this to allow the interior color of red to then disappear.

Can you please add the criteria to your code, where if a value is entered for this column E it will not produce an interior red condition if an entry or value is present there?

Thank you so very much!!
pinaceous

 

[Zot]

Just need to change the range to trigger from B to F
Is this meet your condition?

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)
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

 

[Zot]

Opps I think that is not correct. Try this.

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)
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
    Else
        Range("E" & n).Interior.ColorIndex = xlNone
    End If
End If

End Sub

 

[Pinaceous]

Hi Zot,

This is interesting, it is not doing anything for me. Does it work for you on a test page?

Thanks,
pinaceous

 

[Zot]

Hi Zot,

This is interesting, it is not doing anything for me. Does it work for you on a test page?

Thanks,
pinaceous

Just logic change. Nothing special to it. It did work before, right? Well, it is working fine on my test sheet. You copied it on sheet module, right?

If only 1 to 3 cells occupied in B, C, D or F; no highlight in E. If B, C, D,, F occupied; E would be RED. If B, C, D, E, F occupied; no highlight. My sample results

Book1
	A	B	C	D	E	F
1
2		b	c	d		f
3			c	d	e	f
4		b	c	d	e	f
5					e
6
Sheet1

 

[Pinaceous]

Hi Zot,

Yes, your code works but not sure what I did!

I apologize for not testing it out on a new worksheet.

Thank you for suggesting that!

Now for some reason I have to find out why it doesn't work out on my original workbook.

Thanks again!
pinaceous

 

[Zot]

Hi Zot,

Yes, your code works but not sure what I did!

I apologize for not testing it out on a new worksheet.

Thank you for suggesting that!

Now for some reason I have to find out why it doesn't work out on my original workbook.

Thanks again!
pinaceous

I have older version of Excel but one thing I noticed with Excel 2016, once any macro on an opened workbook failed or crashed, macro on other opened workbooks will also seized to work. You need to close all instances of Excels to make the macro work again. I also noticed that crashed of event triggered function in one of workbook also does the same even though macro might still work.

 

[Pinaceous]

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