VBA If Row then Value

[Pinaceous]

Good Day,

I'm trying to create a vba sub where if the row contains values across its Columns B, C, D, E and if that row's column F is blank then it gets a highlighted red cell.

If the values across its row for Columns B, C, D, E are missing then there will be no highlighted red cell for its Column F.

Once a value is placed in that highlighted red cell then the highlighted red cell will disappear.




Please let me know, if you need any help!

Thank you,
pinaceous

 

[eiloken]

you can edit sub test2 like this:

Sub test2(ByVal xCll As Range) 'this sub change color for single row
    Dim i As Integer
    Dim xCond As Boolean
    For i = 0 To 3 'loop for each cell in column B to E to find empty cell
        If IsEmpty(xCll.Offset(, i)) Then
            xCond = True
        Else
            xCond = False
        End If
    Next i
    If xCond = False And InStr(xCll.Offset(, 4).Value, "123") = 0 Then
        If IsEmpty(xCll.Offset(, 4)) Then 'condition when not found empty cell in same row and cell in column F is empty
            xCll.Offset(, 4).Interior.Color = RGB(255, 0, 0)
        esle
            xCll.Offset(, 4).Interior.Pattern = xlNone
        End If
End Sub

Sorry, i missing this:

Sub test2(ByVal xCll As Range) 'this sub change color for single row
    Dim i As Integer
    Dim xCond As Boolean
    For i = 0 To 3 'loop for each cell in column B to E to find empty cell
        If IsEmpty(xCll.Offset(, i)) Then
            xCond = True
        Else
            xCond = False
        End If
    Next i
    If xCond = False Then
        If IsEmpty(xCll.Offset(, 4)) Then 'condition when not found empty cell in same row and cell in column F is empty
            xCll.Offset(, 4).Interior.Color = RGB(255, 0, 0)
            xCll.Offset(, 4).Value = 123
        esle
            If xCll.Offset(, 4).Value <> 123 And InStr(xCll.Offset(, 4).Value, "123") <> 0 Then
                xCll.Offset(, 4).Interior.Pattern = xlNone
            End If
        End If
    End If
End Sub

 

[eiloken]

Sorry, i missing this:

Sub test2(ByVal xCll As Range) 'this sub change color for single row
    Dim i As Integer
    Dim xCond As Boolean
    For i = 0 To 3 'loop for each cell in column B to E to find empty cell
        If IsEmpty(xCll.Offset(, i)) Then
            xCond = True
        Else
            xCond = False
        End If
    Next i
    If xCond = False Then
        If IsEmpty(xCll.Offset(, 4)) Then 'condition when not found empty cell in same row and cell in column F is empty
            xCll.Offset(, 4).Interior.Color = RGB(255, 0, 0)
            xCll.Offset(, 4).Value = 123
        esle
            If xCll.Offset(, 4).Value <> 123 And InStr(xCll.Offset(, 4).Value, "123") <> 0 Then
                xCll.Offset(, 4).Interior.Pattern = xlNone
            End If
        End If
    End If
End Sub

oh sorry, this code had some mistake, you can try this:

Sub test2(ByVal xCll As Range) 'this sub change color for single row
    Dim i As Integer
    Dim xCond As Boolean
    For i = 0 To 3 'loop for each cell in column B to E to find empty cell
        If IsEmpty(xCll.Offset(, i)) Then
            xCond = True
        Else
            xCond = False
        End If
    Next i
    If xCond = False Then
        If IsEmpty(xCll.Offset(, 4)) Then 'condition when not found empty cell in same row and cell in column F is empty
            xCll.Offset(, 4).Interior.Color = RGB(255, 0, 0)
            xCll.Offset(, 4).Value = 123
        esle
            If xCll.Offset(, 4).Value <> 123 And InStr(xCll.Offset(, 4).Value, "123") <> 0 Then
                xCll.Offset(, 4).Interior.Pattern = xlNone
            Else
                xCll.Offset(, 4).Interior.Color = RGB(255, 0, 0)
                xCll.Offset(, 4).Value = 123
            End If
        End If
    End If
End Sub

 

[Pinaceous]

Wow, thank you so much!

 

[eiloken]

Wow, thank you so much!

you're welcome bro

 

[Jaafar Tribak]

I know eiloken has kindly offered you the code but why not use Conditional formatting ? It is easier and won't need macros to be enabled

 

[Pinaceous]

Hey Jaafar!

I thought about that but I'd prefer the vba method because of the nature of the sheet but I'll keep that in mind.

Thank you,
pinaceous

 

[Pinaceous]

Jaafar,

What code formula could I use in regard to the Conditional formatting?

Thanks,
Pinaceous

 

[Jaafar Tribak]

Jaafar,

What code formula could I use in regard to the Conditional formatting?

Thanks,
Pinaceous

Select the relevant cells in column F:F and add the following CF formula, assuming F1 is the first cell in column F:F.
=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)

 

[Pinaceous]

Select the relevant cells in column F:F and add the following CF formula, assuming F1 is the first cell in column F:F.
=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)

Hey Jaafar Tribak!

I was testing out the CF of the code of the

=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)[/B]

which applies to

=$F$11:$F$180

from your suggestion:

But something weird happens here and I'm not sure if it is from your code or not.

For example, the number the 299, I would expect it to highlight its cell red when I remove that number.

But, it will provide the cell 10 rows down from it in that column a highlighted red cell. In using this example, the number 140 will be highlighted red on this snapshot if I delete the number 299.

Is this something that you can comment on and/or fix?

Thanks!
pinaceous

 

[Pinaceous]

Hey Jaafar Tribak!

I was testing out the CF of the code of the

=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)[/B]

which applies to

=$F$11:$F$180

from your suggestion:

View attachment 99680View attachment 99681

But something weird happens here and I'm not sure if it is from your code or not.

For example, the number the 299, I would expect it to highlight its cell red when I remove that number.

But, it will provide the cell 10 rows down from it in that column a highlighted red cell. In using this example, the number 140 will be highlighted red on this snapshot if I delete the number 299.

Is this something that you can comment on and/or fix?

Thanks!
pinaceous

=AND(LEN($B11),LEN($C11),LEN($D11),LEN($E11),LEN($F11)=0)

Okay, I got it!

Thanks!
-p