IF Statement when multiple conditions are met VBA

Kat31409

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

So I am trying to write a script that if column d has a value in it and column g has false then nothing happens. However if column d has a value and column g does not equal false, then it should turn red.

1615760943858.png


Here are the two scripts I have written:

Public Sub Test1()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastrow
For j = 1 To lastrow
If Cells(i, 4).Value <> "" Then
If Cells(j, 7) <> "False" Then
Cells(j, 7).Interior.Color = vbRed
End If
End If
Next
Next

End Sub

--------------------------------------------------------------------------------------------
Public Sub Test2()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastrow
For j = 1 To lastrow
If Cells(i, 4).Value <> "" And Cells(j, 7) <> "False" Then
Cells(j, 7).Interior.Color = vbRed
End If
Next
Next

End Sub
---------------------------------------------------------------------------------------------

There both extremely similar and both work on the first row, however it is not looping through any of the other row. Let me know if you have any ideas!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

rhombus4

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
For i = 1 To lastrow
If Cells(i, 4).Value <> "" And Cells(i, 7) <> "False" Then
Cells(i, 7).Interior.Color = vbRed
End If
Next i
 

Kat31409

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
For i = 1 To lastrow
If Cells(i, 4).Value <> "" And Cells(i, 7) <> "False" Then
Cells(i, 7).Interior.Color = vbRed
End If
Next i
Thanks for taking the time to reply! I tried it but it's not looping, only does the first row.
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Have you got data in column 1

In your first post you posted
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

If you haven't got data in 1st column that's why it only runs to the 1st row

When trying to find the last row you need to use a column that has data in the last row of your data. Has any column got some data on the last row you want to use

You could always use code below and manually adjust how far you want to loop
VBA Code:
For i = 1 To 100
If Cells(i, 4).Value <> "" And Cells(i, 7) <> "False" Then
Cells(i, 7).Interior.Color = vbRed
End If
Next i
 
Solution

Kat31409

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Have you got data in column 1

In your first post you posted
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

If you haven't got data in 1st column that's why it only runs to the 1st row

When trying to find the last row you need to use a column that has data in the last row of your data. Has any column got some data on the last row you want to use

You could always use code below and manually adjust how far you want to loop
VBA Code:
For i = 1 To 100
If Cells(i, 4).Value <> "" And Cells(i, 7) <> "False" Then
Cells(i, 7).Interior.Color = vbRed
End If
Next i
Thanks it works now! The issue was that I was referencing the first column, instead of the 4th. Thanks for bringing that out and helping me!
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your welcome. Thanks for letting me know it works 😁

PS, you could have avoided VBA altogether and done a conditional format for column G
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,019
Members
416,892
Latest member
Bensch

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
Top