How to use If/Then in VBA code
Category: [Excel] Demo Available
How to use If/Then in VBA code
Requirement:
[If D10 is greater than 90] or [If E10 equals 2.6 or is greater than 11.99] then fill A10 red
If A10 is filled red and matches any other values in Column A, fill those red too.
Repeat for every row after 10.
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1203667-if-then
Solution:
Code:
Sub Button1_Click()
Dim sh As Worksheet
Dim LstRw As Long
Dim Rng As Range, c As Range
'-----------------------------
Dim Lr As Long
Dim Lrng As Range, L As Range
Set sh = Sheets("Sheet1")
With sh
LstRw = .Cells(.Rows.Count, "D").End(xlUp).Row
Lr = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Lrng = .Range("A10:A" & Lr)
Set Rng = .Range("D10:D" & LstRw)
For Each c In Rng.Cells
If c > 90 Or c.Offset(, 1) = 2.6 Or c.Offset(, 1) > 11.99 Then
Cells(c.Row, "A").Interior.Color = vbRed
'------------------
For Each L In Lrng.Cells
If L = .Cells(c.Row, 1).Value Then L.Interior.Color = vbRed
Next L
'-------------------
End If
Next c
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by davesexcel.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use Excel VBA to delete rows in a column based on a range of cells in another column |
| How to use VBA to SUM result in last row in columns A:A |
| How to use VBA code to highlight rows |
| How to use VBA code for multiple IF conditions |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.