remove colours by vba based on conditions in excel 1

[rider1234]

all files are located in desktop

vba is placed in seperate file

only 1 file is opened and that is vba code placed file so for this process we have to open the file as per condition and after the process completed all files should be saved and closed except vba placed file
If column R of 1.xls file is not in minus(-1,-0.5 or xyz any negative number) then see the column E data of 1.xls and open 2.xlsx and match column E of 1.xls with column A of 2.xlsx
and if it matches then look for any highlighted colour in that row and if any highlighted cell in that row is found then remove the highlighted colour and save the file and close all the file
So plz have a look and do needful

https://files.engineering.com/getfile.aspx?folder=03715ec4-8877-4bd4-a498-b626713a44d7&file=1.xls

https://files.engineering.com/getfile.aspx?folder=3a11be93-78ac-4c1a-a0e7-cac511104f5d&file=2.xlsx

 

[SkipVought]

Hi,

What code do you have thus far? Plz post.

It would seem to me that you would
1) open both workbooks 1 & 2,
2) loop thru column R in 1, clearing the color shading in 2 for the corresponding matches and
3) after completing the loop thru column R, save 2 and
4) finally close both workbooks.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

U r right Sir
I am new to vba so i was unable to write a code
so plz guide

 

[SkipVought]

only 1 file is opened and that is vba code placed file…

You are presuming that you have such a file.

If you have no such file, then you should not presume to have stated a process that dies not exist.

So, turn on your macro recorder and record opening each of your workbooks, saving workbook 2 and closing both.

Then please post your recorded code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Sub Code()
Dim wbk1 As Workbook
Dim wsh1 As Worksheet
Dim wbk2 As Workbook
Dim wsh2 As Worksheet

Application.ScreenUpdating = False

Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
Set wsh1 = wbk1.Worksheets(1)

Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\2.xlsx")
Set wsh2 = wbk2.Worksheets(1)


End If
End If
Next r2

Application.DisplayAlerts = False
wbk1.Close SaveChanges:=True
wbk2.Close SaveChanges:=True
Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub










I know only this much how to open a file and save it

 

[SkipVought]

Sub Code()
    Dim wbk1 As Workbook
    Dim wsh1 As Worksheet
    Dim wbk2 As Workbook
    Dim wsh2 As Worksheet
   [b] Dim r1 As Range, vRow2 As Variant, sLookup As String
[/b]    
    Application.ScreenUpdating = False
    
    Set wbk1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")
    Set wsh1 = wbk1.Worksheets(1)
    
    Set wbk2 = Workbooks.Open(ThisWorkbook.Path & "\2.xlsx")
    Set wsh2 = wbk2.Worksheets(1)
[b]    
    With wsh1
        For Each r1 In .Range(.Cells(2, "R"), .Cells(2, "R").End(xlDown))
            If r1.Value < 0 Then
                sLookup = .Cells(r1.Row, "E").Value
                vRow2 = Application.Match(sLookup, wsh2.Range("A:A"), 0)
                If Not IsError(vRow2) Then _
                    wsh2.Rows(vRow2).Interior.ColorIndex = xlColorIndexNone
            End If
        Next
    End With
 [/b]   
    Application.DisplayAlerts = False
    wbk1.Close SaveChanges:=True
    wbk2.Close SaveChanges:=True
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rider1234]

Thnx Skipvought Sir for giving ur precious time and great support to this post