VBA If Row then Value

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
1,124
Office Version
  1. 365
Platform
  1. Windows
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.

Capture.jpg



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
 
you can edit sub test2 like this:
VBA Code:
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:
VBA Code:
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
 
Last edited:
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sorry, i missing this:
VBA Code:
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:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Jaafar,

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

Thanks,
Pinaceous
 
Upvote 0
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)
 
Upvote 1
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
VBA Code:
=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)[/B]
which applies to
VBA Code:
=$F$11:$F$180
from your suggestion:

Capture.jpg
Capture1.jpg


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
 
Upvote 0
Hey Jaafar Tribak!

I was testing out the CF of the code of the
VBA Code:
=AND(LEN($B1),LEN($C1),LEN($D1),LEN($E1),LEN($F1)=0)[/B]
which applies to
VBA Code:
=$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
VBA Code:
=AND(LEN($B11),LEN($C11),LEN($D11),LEN($E11),LEN($F11)=0)

Okay, I got it!

Thanks!
-p
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top