If "VIP" shows up anywhere in column A, and column B says "No", highlight the cell in column B red

[VBAProIWish]

Hello All,

Please note that conditional formatting will not work for this. I need the true color of the cell to be red for a macro (run after) that uses the true color of the cell for its calculations.

Conditional formatting does NOT actually turn the true value of a cell a different color. To verify this, right click a cell turned a different color by conditional formatting and select "Format Cells/Fill".

I need a macro that does this...

If "VIP" shows up anywhere in column A, and column B says "No", highlight the cell in column B red



Here's a before macro and after example...

<TABLE style="WIDTH: 460pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=614 border=0><COLGROUP><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 36pt; BACKGROUND-COLOR: white; mso-ignore: colspan" align=middle width=275 colSpan=2 height=48>Before Macro</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=64></TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white; mso-ignore: colspan" align=middle width=275 colSpan=2>After Macro</TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 36pt; BACKGROUND-COLOR: yellow" height=48>Customer Name</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">VIP Customer</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Customer Name</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">VIP Customer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Jane Peabody VIP!</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Jane Peabody VIP!</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Carrie Krate</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Carrie Krate</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Jerry Prister</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Jerry Prister</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>John Smith VIP!</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">John Smith VIP!</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: red">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Matt Viper</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Matt Viper</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Greg Thompson</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Greg Thompson</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Brett Smith VIP! cust</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Brett Smith VIP! cust</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Jack McCarthy</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Jack McCarthy</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Susan VIP! Flathers</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Susan VIP! Flathers</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD></TR></TBODY></TABLE>


Also note the customer name "Matt Viper", this should NOT be turned red because it is part of his name. " VIP!" should have an exclamation mark after it in order for it to be highlighted red.

So,

VIP - No in VIP Customer Field - Not Red
VIP! - No in VIP Customer Field - Red


Thanks much

 

[VoG]

Conditional Formatting will work for this

Excel Workbook
	A	B
1	Customer Name	VIP Customer
2	Jane Peabody VIP!	Yes
3	Carrie Krate	No
4	Jerry Prister	No
5	John Smith VIP!	No
6	Matt Viper	No
7	Greg Thompson	No
8	Brett Smith VIP! cust	Yes
9	Jack McCarthy	No
10	Susan VIP! Flathers	Yes
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(B2="No",ISNUMBER(FIND(" VIP",A2)))	Abc

 

[VBAProIWish]

VoG,

It won't work unfortunately. Please see my note at the top of my original post.


I need code similiar to this code here...

Sub HL_ColY_w_this_text_Red_if_ColZ_has_this_textv1()
 
   Dim LR As Long
   Dim lc As Integer
   Dim colCN As Integer
   Dim colTF As Integer
   Dim colPA As Integer
   Dim i As Integer
 
   'locate columns containing headers
   With ActiveSheet
      LR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
      lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
      For i = 1 To lc
         If UCase(Cells(1, i)) = "CUSTOMER NUMBER" Then colCN = i
         If UCase(Cells(1, i)) = "CUSTOMER NAME" Then colTF = i
         If UCase(Cells(1, i)) = "VIP CUSTOMER" Then colPA = i
      Next i
      'Process the active sheet
      For i = 2 To LR
         If (UCase(Cells(i, colTF)) = "VIP!") _
            And _
            (Cells(i, colPA) = No) Then
 
            Cells(i, colPA).Interior.ColorIndex = 3
         End If
      Next i
   End With
 
End Sub

I just can't seem to get this code to work when there are other words in the cell in addition to "VIP!" though.


Thanks much for the help

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        If .Value = "No" And InStr(.Offset(, -1).Value, " VIP") > 0 Then .Interior.ColorIndex = 3
    End With
Next i
End Sub

 

[VBAProIWish]

VoG,

Yes, that is so very close!

Could we turn this into an array like this...?

       If .Value = [COLOR=red][COLOR=black]"No",[/COLOR] "Nope", "NA"[/COLOR] And InStr(.Offset(, -1).Value, " VIP", [COLOR=red]"EIP", "YIP"[/COLOR]) > 0

and make it case InSeNsiTiVE?

I tried it with "NO" and it didn't work, but when I tried it with "No" it DID work.

Thanks much!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        If IsNumeric(Application.Match(LCase(.Value), Array("no", "nope", "na"), 0)) And InStr(.Offset(, -1).Value, " VIP") > 0 Then .Interior.ColorIndex = 3
    End With
Next i
End Sub

 

[VBAProIWish]

Yes, that worked, can you do that array for the other end to ("VIP", "EIP", "YIP"). It was at the end of my last post so you might've missed it.

Thanks!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        If IsNumeric(Application.Match(LCase(.Value), Array("no", "nope", "na"), 0)) And (InStr(.Offset(, -1).Value, " VIP") > 0 Or InStr(.Offset(, -1).Value, " EIP") > 0 Or InStr(.Offset(, -1).Value, " YIP") > 0) Then .Interior.ColorIndex = 3
    End With
Next i
End Sub

 

[VBAProIWish]

VoG,

Okay, that worked awesome! Thanks for that!

Now, since you have good knowledge of this macro and it's fresh in your mind, can we do it the opposite way?


What I mean by that is this...

If column B says "yes" or "YES" (case inSenSitIVe in the "VIP Customer" field), and there is no "VIP", "EIP" or "YIP" anywhere in the same row in column A (the Customer Name" Field), highlight the cell in column B red.

This will be a tremendous help!

Thanks!

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        If LCase(.Value = "yes") And InStr(.Offset(, -1).Value, " VIP") = 0 And InStr(.Offset(, -1).Value, " EIP") = 0 And InStr(.Offset(, -1).Value, " YIP") = 0 Then .Interior.ColorIndex = 3
    End With
Next i
End Sub