VBA - Color Row with If

[ExcelMercy]

Hey Everyone,


I'm looking for macro that will check two conditions if both check out then color the rows on one of the sheets.

In a nutshell:

If sheet:Market_Total's column B (Market ID) matches sheet:Market_Confirm's column A (Market ID) 
   True: If sheet:Market_Total's column E (Name id) is "A1111"
          True: Color Market_Confirm corresponding row backcolor Blue
          False: Do Nothing
   False: Do Nothing

Example:::




Market_Total

Type

Market ID

Order by

System Code

Name id

First Name

Middle Initial

Last Name

Address 1

Address 2

City

State

Postal code

1

213546

*

AP_123_Lo_4

A1234

Billy

C

Smith

111 N Street

Philadelphia

PA

12345

1

432452

*

AP_123_Lo_5

A1235

Jacob

Johnson

123 S Street

New Orleans

LA

84001

1

3425267

*

AP_123_Lo_6

A1111

Sue

Doe

123 Main St

Atlanta

GA

65431

1

8798567

*

AP_123_Lo_7

A1237

Becky

A

Smith

123 NorthWest Main Rd

Nashville

TN

45678

2

679732542

*

AP_123_Lo_8

A1238

Stacy

Marshall

9483 Walkway Dr

Houston

TX

54634

2

3242368

*

JF_123_Lo_1_SYS

A1111

Larence

S

Donald

2143 Systems Avn

New Orleans

LA

84001

1

6775674

*

JF_123_Lo_2

A1240

Kimberly

Jones

123 Timber Rd

Nashville

TN

54001

1

53424567

*

JF_123_Lo_2

A1241

Mike

G

Gareld

136 South rd

Philadelphia

PA

45201

1

8798567

*

HG_123_Lo_1_SYS

A1111

Becky

A

Smith

788 Landing Rd

Nashville

TN

45678

2

679732542

*

HG_123_Lo_1_SYS

A1243

Stacy

Marshall

3 Moore Dr

Philadelphia

PA

85201

2

3242368

*

HG_123_Lo_1_SYS

A1244

Larence

S

Donald

212 Lake Drive

Philadelphia

PA

95201

1

6775674

*

HG_123_Lo_2_SYS

A1111

Kimberly

Jones

1677 Trees Rd

New Orleans

LA

84001

1

53424567

*

HG_123_Lo_2_SYS

A1246

Mike

G

Gareld

13455 Northsouth Rd

Philadelphia

PA

65201

<tbody>
</tbody>

Market Confirm

Market ID	System Code	First Name	Middle Initial	Last Name
3242368	JF_123_Lo_1_SYS	Larence	S	Donald
8798567	HG_123_Lo_1_SYS	Becky	A	Smith
679732542	HG_123_Lo_1_SYS	Stacy		Marshall
3242368	HG_123_Lo_1_SYS	Larence	S	Donald
6775674	HG_123_Lo_2_SYS	Kimberly		Jones
53424567	HG_123_Lo_2_SYS	Mike	G	Gareld

<tbody>
</tbody>

(I can't seem to backcolor on the MrExcel.com forums, so I just changed the text color on here as an example. For the actual macro I would rather the back color change.)




Thanks to anyone who can assist!

 

[V_Malkoti]

Try this:

Sub FindAndHighlight()
    Dim i As Long
    Dim oWSC As Worksheet, oWST As Worksheet
    Dim oFound As Range
    
    Set oWSC = ThisWorkbook.Sheets("Market_Confirm")
    Set oWST = ThisWorkbook.Sheets("Market_Total")
    
    i = 2
    While oWSC.Range("B" & i).Value <> ""
        Set oFound = oWST.Range("B:B").Find(what:=oWSC.Cells(i, 1))
        If Not oFound Is Nothing Then
            oWSC.Range("A" & i & ":E" & i).Interior.ColorIndex = 8
        End If
        i = i + 1
    Wend
End Sub

 

[ExcelMercy]

Hey V_Malkoti,


I'm getting all of the cells colored in. I need it to check column E on Market_Totals for, "A1111" too first before coloring.

 

[V_Malkoti]

Condition added:

Sub FindAndHighlight()
    Dim i As Long
    Dim oWSC As Worksheet, oWST As Worksheet
    Dim oFound As Range, sFirst As String
    
    Set oWSC = ThisWorkbook.Sheets("Market_Confirm")
    Set oWST = ThisWorkbook.Sheets("Market_Total")
    
    i = 2
    While oWSC.Range("B" & i).Value <> ""
        Set oFound = oWST.Range("B:B").Find(what:=oWSC.Cells(i, 1))
        If Not oFound Is Nothing Then
            sFirst = oFound.Address
            Do
                If oFound.Offset(0, 3).Value = "A1111" Then oWSC.Range("A" & i & ":E" & i).Interior.ColorIndex = 8
                Set oFound = oWST.Range("B:B").FindNext(oFound)
            Loop Until oFound Is Nothing Or oFound.Address = sFirst
        End If
        i = i + 1
    Wend
End Sub

Also I noticed that in your example you didn't highlight row#4 although it looks same as row#1 (Larence S Donald). Is there another condition involved?

 

[ExcelMercy]

The 2nd Larence S Donald has a different Name id. The condition would be requiring the "A1111" Name id for the corresponding Market ID.


BUT --- You know what. I'm stupid. Having Market_Totals not have another unique ID to bounce it off of it'll catch both.


Nevermind. You're awesome :P Thanks for the help

 

[Eric W]

You can also use Conditional Formatting to accomplish this.

On your Market_Confirm sheet, select your table (A1:E7 in your example). Then click on Conditional Formatting --> New Rule --> Use a formula to determine which cells to format.

Enter this formula:
=SUMPRODUCT(--($A1=Market_Total!$B$1:$B$13),--(Market_Total!$E$1:$E$13="A1111"))

Then select Format... and choose your background color.

 

[ExcelMercy]

Hey V_Malkoti,


Would it possible for you to add some comments explaining what is happening. I'm having trouble reverse-engineering it.


Like what is the "B" targeting on the part:
While oWSC.Range("B" & i).Value <> ""

 

[V_Malkoti]

With comments:

Sub FindAndHighlight()
    Dim i As Long
    Dim oWSC As Worksheet, oWST As Worksheet
    Dim oFound As Range, sFirst As String
    
    Set oWSC = ThisWorkbook.Sheets("Market_Confirm")
    Set oWST = ThisWorkbook.Sheets("Market_Total")
    
[COLOR=#006400]    ' Start with row #2[/COLOR]
    i = 2
[COLOR=#006400]    ' Continue looping if value in column B of current row is not blank[/COLOR]
    While oWSC.Range("B" & i).Value <> ""
[COLOR=#006400]        ' search for market id value from Confirm sheet into Total sheet[/COLOR]
        Set oFound = oWST.Range("B:B").Find(what:=oWSC.Cells(i, 1))
[COLOR=#006400]        ' If a match is found[/COLOR]
        If Not oFound Is Nothing Then
[COLOR=#006400]            ' Note the address of cell found[/COLOR]
            sFirst = oFound.Address
            Do
[COLOR=#006400]                ' If name id = A1111 then highlight the row in Confirm sheet[/COLOR]
                If oFound.Offset(0, 3).Value = "A1111" Then oWSC.Range("A" & i & ":E" & i).Interior.ColorIndex = 8
[COLOR=#006400]                ' See if there are more than one rows with that market id in Total sheet[/COLOR]
                Set oFound = oWST.Range("B:B").FindNext(oFound)
            Loop Until oFound Is Nothing Or oFound.Address = sFirst
[COLOR=#006400]            ' Keep on looping until all matching rows are processed or no other match is found[/COLOR]
        End If
[COLOR=#006400]        ' increment row counter for Confirm sheet[/COLOR]
        i = i + 1
    Wend
End Sub