My Excel sheet looks like this:
我的Excel表格是这样的:
+=========+=========+=================================+======================================+
| MPN | BRAND | TITLE | URL |
+=========+=========+=================================+======================================+
| GB38905 | GRIFFIN | All-Terrain Case for iPhone ... | https://www.example.com/gb38905.html |
+---------+---------+---------------------------------+--------------------------------------+
.. and I need to highlight the background of the row to green|orange|red based on these conditions (all case insensitive):
. .我需要根据这些条件将行背景突出为绿色|橙色|红色(不区分大小写):
- URL AND TITTLE contain MPN -> GREEN
- URL和TITTLE包含MPN ->绿色
- URL OR TITTLE contain MPN -> ORANGE
- URL或TITTLE包含MPN ->橙色。
- URL OR TITTLE DO NOT contain MPN -> RED
- URL或TITTLE不包含MPN ->红色
This is my first attempt at using VBA:
这是我第一次尝试使用VBA:
Option Compare Text
Sub MySub()
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("A1: E17361")
For Each row In rng.Rows
For Each cell In row.Cells
MPN = Range("C1").Value
If InStr(Range("C3").Value, MPN) And InStr(Range("C4").Value, MPN) > 0 Then
cell.Interior.Color = vbGreen
ElseIf InStr(Range("C3").Value, MPN) Or InStr(Range("C4").Value, MPN) > 0 Then
cell.Interior.Color = vbOrange
Else
cell.Interior.Color = vbYellow
End If
Next cell
Next row
End Sub
.. and it obviously doesn't work. It turns ALL rows to green.
. .很明显它不起作用。它把所有的行都变成绿色。
1 个解决方案
#1
1
Option Compare Text
Sub MySub()
Dim rng As Range
Dim row As Range
Dim MPN, u As Boolean, t As Boolean, clr as long
Set rng = Range("A1:E17361")
For Each row In rng.Rows
MPN = row.Cells(1).Value
u = InStr(row.cells(4).Value, MPN) > 0
t = instr(row.cells(3).Value, MPN) > 0
If u And t Then
clr = vbGreen
ElseIf u Or t Then
clr = vbMagenta
Else
clr = vbYellow
End If
row.Interior.Color = clr
Next row
End Sub
#1
1
Option Compare Text
Sub MySub()
Dim rng As Range
Dim row As Range
Dim MPN, u As Boolean, t As Boolean, clr as long
Set rng = Range("A1:E17361")
For Each row In rng.Rows
MPN = row.Cells(1).Value
u = InStr(row.cells(4).Value, MPN) > 0
t = instr(row.cells(3).Value, MPN) > 0
If u And t Then
clr = vbGreen
ElseIf u Or t Then
clr = vbMagenta
Else
clr = vbYellow
End If
row.Interior.Color = clr
Next row
End Sub