Conditional formatting based on cell value

Question

My Excel sheet looks like this:

+=========+=========+=================================+======================================+
|   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 OR TITTLE contain MPN -> ORANGE
URL OR TITTLE DO NOT contain MPN -> RED

This is my first attempt at using 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.

Instr returns an integer, not a Boolean. Replace InStr(foo, bar) = True with InStr(foo, bar) > 0 or just InStr(foo, bar) And InStr(foo, baz). — Comintern
– Comintern, Commented Oct 21, 2016 at 21:29
@RobertColumbia YES, it does not work. And I can't debug cause I'm using VBA for the first time ever so I'm clueless. I have done enough research and put effort to ask a perfectly valid question now! — eozzy
– eozzy, Commented Oct 21, 2016 at 21:35
@Comintern Ah thanks, but it now turns everything to green so still missing something. — eozzy
– eozzy, Commented Oct 21, 2016 at 21:52

eozzy · Accepted Answer · 2016-10-22 00:21:27Z

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

edited Oct 22, 2016 at 0:21

eozzy

69.3k111 gold badges287 silver badges448 bronze badges

answered Oct 21, 2016 at 22:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eozzy Over a year ago

Super! Almost works but for example

TL-PA4016P KIT	TP-Link	AV500 Powerline Passthrough Starter Kit - TP-Link	http://www.tp-link.com.au/products/details/cat-18_TL-PA4016P-KIT.html

<- this was turned green when obviously it doesn't fulfll the And condition.

eozzy Over a year ago

My bad, the column numbers were misaligned. Awesome, thanks heaps!

eozzy Over a year ago

btw vbYellow is not a valid color, thats also my bad code but just for reference.

Collectives™ on Stack Overflow

Conditional formatting based on cell value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related