VBA code for cells in row

[Pinaceous]

Good Day All,

I'm trying to work on a code, where if a row has data in Columns B:E & G, and any information contained from that rows Column H:Z that when Column F is blank under these conditions that its interior cell color will turn red.

Likewise, if under this conditions Column F is not blank, the code will do nothing.

Please note, that this proposed criterion ignores Column A's information.

Can someone help me sort out a VBA for this?

Thank you in advance!

Respectfully,
pinaceous

 

[DanteAmor]

You can use a conditional format like this:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
2		24	47	10	38	45	34	26	11	9	2	14	27	35	42	3	19	33	47	13	48	32	36	34	15	15
3		20	32	17	23		46	3	24	29	3	4	5	14	13	37	46	49	46	3	42	7	21	41	34	22
4		23	38	49	35	32	37	30	2	9	28	50	45	29	14	18	18	32	16	3	23	46	8	42	4	43
5		12	36	28	48		45	42	5	16	8	26	37	49	12	30	6	9	27	49	39	32	27	30	29	27
6		28	46	50	12		35	25	6	36	45	48	8	5	38	18	48	23	39	49	4	2	43	24	4	44
7		24	1	19	6
8		9	37	48	33	20	38	30	19	18	22	17	24	35	44	38	31	38	29	36	36	40	11	45	43	26
9		28	50	45	29		23
Hoja3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F9	Expression	=AND(COUNTA($B2:$E2,$G2:$Z2)=24,$F2="")	text	NO

 

[Pinaceous]

Hi DanteAmor,

How do I do that? Can you kindly break it down for me? I'm lost.

Is this under the 'New Formatting Rule'?

Thank you!
pinaceous

 

[DanteAmor]

Can you kindly break it down for me?

Of course:

1. Select the cells you want to format, for example Select cells F2 through F100.
2. On the Home tab, click Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.

4. In the next box, type the formula:

=AND(COUNTA($B2:$E2,$G2:$Z2)=24,$F2="")

5. Click custom format.

6. In the Format Cells dialog box, click the Fill tab.
7. Select Red color.
8. Click OK until the dialog boxes are closed.

 

[Pinaceous]

Hi DanteAmor,

I really appreciate that you helped me with the conditional formatting!

Okay, so now I believe that your formula needs to be adjusted because when your code applies to the sheet it looks like this:

But I would like it to do this:

According to:

"I'm trying to work on a code, where if a row has data in Columns B:E & G, and any information contained from that rows Column H:Z that when Column F is blank under these conditions that its interior cell color will turn red.

Likewise, if under this conditions Column F is not blank, the code will do nothing."


Can you please adjust your formula? And again, I really do appreciate that you coached me through the CF.

Respectfully,
pinaceous

 

[DanteAmor]

In my example I always referenced the cells starting at row 2.
In your data you have row 2 and row 1.
Change the 2 in the formula to the number 1:

 

[bebo021999]

One way:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26
2		24	47	10	38	45	34	26	11	9	2	14	27	35	42	3	19	33	47	13	48	32	36	34	15	15
3		20	32	17	23		46	3	24	29	3	4	5	14	13	37	46	49	46	3	42	7	21	41	34	22
4		23	38	49	35	32	37	30	2	9	28	50	45	29	14	18	18	32	16	3	23	46	8	42	4	43
5		12	36	28	48		45	42	5	16	8	26	37	49	12	30	6	9	27	49	39	32	27	30	29	27
6		28	46	50	12		35	25	6	36	45	48	8	5	38	18	48	23	39	49	4	2	43	24	4	44
7		24	1	19	6
8		9	37	48	33	20	38	30	19	18	22	17	24	35	44	38	31	38	29	36	36	40	11	45	43	26
9		28	50	45	29		23
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F1:F9	Expression	=AND(COUNT(B1:E1)=4,COUNT(G1:Z1)>0,F1="")	text	NO

 

[bebo021999]

And, here is VBA code:

Option Explicit
Sub color()
Dim lr&, cell As Range, rng As Range
Set rng = Range("F1:F" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
For Each cell In rng
    cell.Interior.color = xlNone
    With WorksheetFunction
        If .Count(cell.Offset(, -4).Resize(1, 4)) = 4 And .Count(cell.Resize(, 21)) > 0 Then
            cell.Interior.color = vbRed
        End If
    End With
Next
End Sub

 

[DanteAmor]

Can you please adjust your formula? And again, I really do appreciate that you coached me through the CF.

Try this formula:

=AND(COUNTA($B1:$E1,$G1:$Z1)=24,$F1="")

 

[Pinaceous]

Okay thanks guys! I'll have to play around with it to see which one is best! Appreciate all of your efforts!