# Hight Row Based on Cell Value & Interior Color



## mayoung (Dec 19, 2022)

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 (Dec 19, 2022)

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 (Dec 19, 2022)

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 (Dec 19, 2022)

missing quote
And Sheets("WIP-MAIN*"*)


----------



## mayoung (Dec 19, 2022)

mayoung said:


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





Micron said:


> 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 (Dec 19, 2022)

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 (Dec 19, 2022)

Micron said:


> 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 (Dec 19, 2022)

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 (Dec 19, 2022)

Micron said:


> 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..


----------

