VBA Column F interior red

[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 but I cannot get the VBA code to work.

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

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

 

[Peter_SSs]

From you description it sounds like you might need something a bit more like this.

If .Count(cell.Offset(, -4).Resize(1, 6)) = 5 And .Count(cell.Offset(, 2).Resize(, 19)) > 0 Then

 

[Pinaceous]

Hey Peter_SSs,

Your code definitely works!

But now once a value is entered that no longer satisfies the criteria above, can you modify your code to then make that interior red cell goes away?

For example, I've entered in "123" in Column F & Row 14 into an interior red cell condition from your code.

Can you now revise your code to provide that when a value such as what was entered into Column F & Row 14 goes back to a non-interior red color condition? As can be seen here:

Please let me know, when you get a chance or if you need any further details.

Thank you!
pinaceous

 

[Peter_SSs]

If the values can change then why not use Excel's built-in Conditional Formatting - designed exactly for that sort of situation?
You can apply it manually or by code.

Sub CF()
  With Range("F2:F" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F2="""",COUNT(B2:G2)=5,COUNT(H2:Z2))"
    .FormatConditions(1).Interior.color = vbRed
  End With
End Sub

 

[Pinaceous]

Hi Peter_SSs,

I would prefer the VBA code and will give your Post#4 code a try. I do appreciate that you provided me something to test out!

Thank you!
Pinaceous

 

[Pinaceous]

Hey Peter_SSs,

In working with your code from Post#4, do you know if you can help me change the parameter? I noticed an error on my part.

Right now, the code behaves where if an entry is typed into G:Z it does not provide an interior red cell in F.

For example, in working with line 3, if I type in "1" in G3 it does not provide an interior red cell in F3.

Do you know if you can help modify the code to produce an interior red cell for F in the range of G:Z? Where if I type in a value using the example above, it will produce an interior red cell for F3?

Right now, it produces an interior red cell based upon this condition below:

Please let me know, if you can help me modify the code or if you would like me to explain it better.

Thank you!
Pinaceous

 

[Pinaceous]

Oh wait I got it!

Sub CF()
  With Range("F2:F" & Range("B" & Rows.Count).End(xlUp).Row)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F2="""",COUNT(B2:F2)=4,COUNT(G2:Z2))"
    .FormatConditions(1).Interior.color = vbRed
  End With
End Sub

 

[Peter_SSs]

Right now, the code behaves where if an entry is typed into G:Z it does not provide an interior red cell in F.

For example, in working with line 3, if I type in "1" in G3 it does not provide an interior red cell in F3.

That is because your original requirement was for the red condition below to be true and the blue condition below to be true.
In your first picture above, for row 3 the blue condition is not true hence no colour applied to to column F.

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.

If you your requirements have changed please clearly spell out the new condition(s).