asked on

Excel Lookup / Match across two worksheets and return red cells on both in one list

Hi Experts

Ive just received some excellent assistance on the attached workbook however there is one more (final) item Im struggling with

On the first tab - Completed - to the right is a section saying Total of Red on Both

Is there a way to look across two worksheets - Results Units and Results Costs - and if a red cell appears on both, to list this on the front sheet under the above title - or set it up on both so I can drag the results onto the Completed tab?

If it did not involve conditional formatting and colour I dont believe I would have an issue however Im not sure if its possible to include the colour variable into the lookup / syntax?

Any suggestions would be welcome

J
Copy-of-Cycle-count-pick-face-map-2.xlsm

Norie

Jase

Do both sheets have the same values in A1:AY34?

Jase Alexander

ASKER

HI Norie

yes they are the same 'location' / cell references on both sheets

So basically if they location references appear red on both sheets, is there a way to list them ?

The fact that they are conditionally formatted is also a bit tricky I would think ....

Any suggestions?

J

Norie

Jase

Are you looking for a UDF for this or something else?

Jase Alexander

ASKER

HI Norie

yes so Im looking for a solution to this problem in looking over two worksheets that have red coloured cells

If the cells match on the two worksheets, then it returned the result in either a list form or in some other format so I can extract it onto the Completed tab

Thanks
J

Jacques Geday

Hello
I read the threads and noted your last comment

If the cells match on the two worksheets, then it returned the result in either a list form or in some other format so I can extract it onto the Completed tab

Let me be specific:
1) Do you mean compare data that is in sheet: Results Units and sheet Results Cost ?
2) So you want to report on sheet Completed the Red at the same spot ?
OR
Have in a new sheet a list of all those cells ?

gowflow

Jase Alexander

ASKER

HI Gowflow

Firstly thank you for the response

So :-
1) Yes - compare the data on the Results Units and Results Costs sheet and ;
2) if there is a red cell that matches on BOTH sheets (in the example, for example, Cell M17 is red on both so the result would return 05_09 if possible) then this is listed on the Completed sheet (on the far right) under the cdell that says Total of Red on Both .... or list on a new sheet as that would also be welcome

Much appreciated
J

Jacques Geday

ok here is my solution that I have included in the attached workbook. Just run the macro ReportCompleted and check the results in sheet Completed.

Sub ReportCompleted()
Dim WSRU As Worksheet, WSRC As Worksheet, WSC As Worksheet
Dim I As Long, J As Long, lCount As Long


'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variable
Set WSRU = Sheets("Results Units")
Set WSRC = Sheets("Results Cost")
Set WSC = Sheets("Completed")

For I = 1 To 34
    For J = 1 To 51
        If WSRU.Cells(I, J).DisplayFormat.Interior.ColorIndex = 3 And WSRC.Cells(I, J).DisplayFormat.Interior.ColorIndex = 3 Then
            WSC.Cells(I, J).Interior.ColorIndex = 3
            lCount = lCount + 1
        End If
    Next J
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "A Ttoal of " & lCount & " red cells were found in both sheets " & WSRU.Name & " and " & WSRC.Name & " respectively.", vbInformation, "Red Cells"

End Sub

Open in new window

Let me know
Gowflow
Cycle-count-pick-face-map-2-V01.xlsm

Jacques Geday

Ooops our answers crossed !!! I colored in Red the same cell if you want otherwise let me know I see you want to report a figure.

Let me know
Gowflow

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

Jase

Wouldn't it be easier to work from the list on the 'Data' worksheet?

You could go down column R and check column S for <-100 and column T for <-150.

Something like this.

Sub ListRed()
Dim arrIn As Variant
Dim arrRed As Variant
Dim I As Long
Dim cnt As Long
    
    arrIn = Sheets("Data").Range("R2", Sheets("Data").Range("T" & Rows.Count).End(xlUp))
    
    ReDim arrOut(1 To UBound(arrIn, 1))
    
    For I = LBound(arrIn, 1) To UBound(arrIn, 1)
        If arrIn(I, 2) < -100 And arrIn(I, 3) < -150 Then
            cnt = cnt + 1
            arrOut(cnt) = arrIn(I, 1)
        End If
    Next I
    
    If cnt > 0 Then
        ReDim Preserve arrOut(1 To cnt)
        Sheets("Completed").Range("BE5").Resize(cnt).Value = Application.Transpose(arrOut)
    End If
    
End Sub

Open in new window

Jase Alexander

ASKER

HI Both

Thank you so much for your help

Gowflow - this was better than I could have asked for - thank you for the suggestion and deviation - its perfect !!

Norie - thank you for the alternative - I will definitely be including this also

Cant thank you guys enough

J

Jacques Geday

Jase Alexander
Glad I could help. We thrive to always deliver 'better than expected'
Gowflow