Hight Row Based on Cell Value & Interior Color

[mayoung]

The code below highlights the Row on the WIP-Summary sheet if a Value match is found on the WIP-MAIN sheet. Which highlights all the rows on the WIP-SUMMARY sheet. Because the summary is based off the main.

Is there away to modify this code that it highlights the Row on the WIP-Summary sheet if a Value match is found on the WIP-MAIN sheet but also the cell interior color on the WIP-MAIN sheet has a cell Interior.Color = RGB(166, 166, 166). So now only certain rows will be highlighted on the summary sheet instead of all of them.

Sub RunMe()

Dim lRow1, lRow2, x As Integer

Windows("ServiceFile.xlsm").Activate

lRow2 = Sheets("WIP-Main").Range("C" & Rows.Count).End(xlUp).Row
Sheets("WIP-Summary").Activate
lRow1 = Range("C" & Rows.Count).End(xlUp).Row
x = 1

For Each cell In Range("C2:C" & lRow1)
    Do
        x = x + 1
        If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") Then
            Rows(cell.Row).Interior.Color = RGB(166, 166, 166)
        End If
    Loop Until x = lRow2
    x = 1
Next cell

End Sub

 

[Micron]

maybe

If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") And Sheets("WIP-MAIN).Cells(x,"C").Interior.Color = RGB(166, 166, 166) Then

If it works, that could be shortened by setting either a sheet object variable to Sheets("WIP-MAIN") or perhaps drill down to the cell with a cell object variable.
You should test on a test sheet or workbook.

 

[mayoung]

Micron please attached screenshot for error message I am getting after inserting your line of code? Any suggestions

Sub RunMe1()

Dim lRow1, lRow2, x As Integer

Windows("ServiceFile.xlsm").Activate

lRow2 = Sheets("WIP-Main").Range("C" & Rows.count).End(xlUp).Row
Sheets("WIP-Summary").Activate
lRow1 = Range("C" & Rows.count).End(xlUp).Row
x = 1

For Each cell In Range("C2:C" & lRow1)
    Do
        x = x + 1
'        If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") And Sheets("WIP-MAIN").Interior.Color = RGB(166, 166, 166) Then
If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") And Sheets("WIP-MAIN).Cells(x,"C").Interior.Color = RGB(166, 166, 166) Then
        Rows(cell.Row).Interior.Color = RGB(166, 166, 166)
        End If
    Loop Until x = lRow2
    x = 1
Next cell

End Sub

 

[Micron]

missing quote
And Sheets("WIP-MAIN")

 

[mayoung]

Micron please attached screenshot for error message I am getting after inserting your line of code? Any suggestions

Sub RunMe1()

Dim lRow1, lRow2, x As Integer

Windows("ServiceFile.xlsm").Activate

lRow2 = Sheets("WIP-Main").Range("C" & Rows.count).End(xlUp).Row
Sheets("WIP-Summary").Activate
lRow1 = Range("C" & Rows.count).End(xlUp).Row
x = 1

For Each cell In Range("C2:C" & lRow1)
    Do
        x = x + 1
'        If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") And Sheets("WIP-MAIN").Interior.Color = RGB(166, 166, 166) Then
If cell.Value = Sheets("WIP-MAIN").Cells(x, "C") And Sheets("WIP-MAIN).Cells(x,"C").Interior.Color = RGB(166, 166, 166) Then
        Rows(cell.Row).Interior.Color = RGB(166, 166, 166)
        End If
    Loop Until x = lRow2
    x = 1
Next cell

End Sub

missing quote
And Sheets("WIP-MAIN")

Ok I figured out the error and ran the code and that worked. So Thank You again Micron. Since the code worked you suggested that it could possibly be shortened. How would you do that?

 

[Micron]

I was referring to only that new line, not the whole thing. If what you have works, I'd stick with it. In order to shorten the line, you'd have to add other lines in order to define the variables and their values, which would be kind of counter-intuitive now that I think about it.

 

[mayoung]

I was referring to only that new line, not the whole thing. If what you have works, I'd stick with it. In order to shorten the line, you'd have to add other lines in order to define the variables and their values, which would be kind of counter-intuitive now that I think about it.

One last question? Currently the code compares two work sheets within the same work book is it also possible to compare between to work books?

Lets say I want to take a work book named Current Weekly Service-WIP and the work book named ServiceFile which I have been working in then compare the two summary sheets between the two work books? Basically doing the same thing but between two work books? Is there much code I would have to add to this?

 

[Micron]

Pretty sure this has been asked and answered thousands of times. Did you look for sample code on how to copy and paste between workbooks?
Excel vba really isn't my forte (as much as Access vba) so all I'd be doing is researching and cobbling something together, which you could probably do and cut out the middle man (that's me).

 

[mayoung]

Pretty sure this has been asked and answered thousands of times. Did you look for sample code on how to copy and paste between workbooks?
Excel vba really isn't my forte (as much as Access vba) so all I'd be doing is researching and cobbling something together, which you could probably do and cut out the middle man (that's me).

Thank You..