Misc. Leonardo1234 rider@1234 vixer. Highlighting. Simple Early stuff. Avinash Introduction

Avinash Singh
https://excelfox.com/forum/showthrea...ll=1#post14092

This is all just a few rough notes, mainly for my later referrence, about the World Record Holder in repeated multiple duplicated cross posting similar questions and blatant lying , ignoring, and total disrespect of all forum members helping him.
Maybe a bot? Maybe organised by excelforum owner? Maybe just a total Psycho? Or just an Autistic like character: a total 100% idiot coupled with 100% motivation/ persistence and inability to stop on a totally futile and pointless quest??

Very far from complete listing, and changing daily…

Eileen’s Lounge.
First Leonardo1234 post was at end of July 2018
http://www.eileenslounge.com/viewtopic.php?f=30&t=30447
Hans got fed up around May/June 2019
http://www.eileenslounge.com/viewtop...252759#p252759
http://www.eileenslounge.com/viewtop...252961#p252961
roger@/umpsbug/leonardo1234/c@1234
zyxw123 started 22 April 2020

excelguru.ca
leonardo1234
https://www.excelguru.ca/forums/show...h-complete-row

VBForums
kinjals
http://www.vbforums.com/showthread.p...75#post5413075

Ozgrid
jhas56788
https://www.ozgrid.com/forum/index.p...38#post1234138
https://www.ozgrid.com/forum/index.p...38#post1234938

TeachExcel
Avinash
https://www.teachexcel.com/talk/3794...te-csv-to-xlsx

MrExcel
hdgfss
https://www.mrexcel.com/board/thread.../#post-5358172

VBAExpress
KAJAL1234
http://www.vbaexpress.com/forum/show...ht=#post402805
kynga


excelforum
dumdumbum
* MoldyBread ( using someone elses’s original account )
sumanjjj
https://www.excelforum.com/excel-pro...ml#post5346727
* Zaeguzah ( original registered 2009, Avinash using only since .. 7th Aug 2020-08-17 https://www.excelforum.com/excel-pro...ml#post5376352
* quirkybox ( using someone elses’s original account )
https://www.excelforum.com/excel-pro...ml#post5381409
kynga
* At excelforum “it” appears to have temporary access to lots of existing acccounts, almost all!! Possibly this “thing” is well “at home there”…. posting poor quality similar questions over and over again… and getting answers… from the “best” people there…



Chandroo
r@1234 Leonardo1234 ANJUS rottenrogue
https://chandoo.org/forum/threads/co...4/#post-253302
https://chandoo.org/forum/threads/vb...-2#post-264459
https://chandoo.org/forum/threads/co...a-macro.44815/
kynga


ExpertsExchange
Sachin Singh
avinash singh
Avinash Singh
https://www.experts-exchange.com/que...-the-file.html
https://www.experts-exchange.com/mem...h-Singh-1.html
https://www.experts-exchange.com/mem...ash-singh.html
https://www.experts-exchange.com/mem...n-Singh-2.html


myonlinetraininghub.com
https://www.myonlinetraininghub.com/...bers-in-series

vbforums
mail2tradesmart
https://www.vbforums.com/showthread....)-by-vba-macro

---

Some of the known usernames. There are very likely a lot more. Both at English speaking forums and at other language speaking forums, such as Hindi
Sachin Singh
avinash singh
Avinash Singh
zyxw123
jhas56788
rider
roger
anjus
shinshan
sumanjjj
Leonardo1234
umpsbug
Kinjals
Tinamishra
kinjal124
sumanjjj123
KAJAL1234
rider1234
r@1234
ANJUS
andy124
rider@1234
fixer
c@1234
vixer
roger@
Leonard
dumdumbum
Ava453644
MoldyBread
Zaeguzah
kynga
quirkybox

Ava453644 awatesunil95@gmail.com 77.111.246.17 June 12 2020 register
103.216.212.90 14 June 2020 looking at fixer stuff after 3rd fixer ban
shiv1823@gmail.com 49.36.49.130 June 14
103.216.212.90

3 oct 2019, 07:40 PM #1
fixer The IP Address is: 103.216.212.92
The host name is: 103.216.212.92
PM: ( http://www.excelfox.com/forum/privat...=newpm&u=12517 )

07-28-2018, 07:22 PM #1
Leonardo1234 singhavinash.201351@gmail.com User: Leonardo1234 (id: 11895)
The IP Address is: 103.216.212.154
The host name is: 103.216.212.154

Fixer suryabhoir121@gmail.com User: fixer (id: 12517)
Sonntag 6 oct 103.216.212.92
The host name is: 103.216.212.92
The IP Address is: 103.216.212.93
The host name is: 103.216.212.93
Mumbai
maharashtra


Bro Subodh is no longer to help me bcoz expertsexchange has started the plans to login and ask question
HansV is not ready to help me
now u r the only hope for me just be with me for 10 question and believe me and my work will be completed bro…….

Most recent acive as dumdumbum quirkybox Zaeguzah at excelforum , zyxw123 at Eileen's Lounge and under "own" name at Experts Exchange
:

Avinash Singh
Mumbai
Hauptstadt des Bundesstaates Maharashtra in Indien

We have to compare each complete row of sheet2 with sheet3 each complete row and if sheet2 complete row match with sheet3 complete row then highlight it with green colour in sheet4 in coloumn B


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hello Leonardo1234,
Welcome to excelfox :)
I have looked at your file, and read your request but I cannot understand quite your output requirement .?? :confused::(:confused:
You say …" compare each complete row of sheet2.."..here is your given Sheet2 from your file:

1 L 1	1 L 1,1 L 1
1 G 1	1 E 1	1 L 1,1 L 1	1 E 1
1 G 1	1 E 1	1 L 1,1 L 1	1 E 1	1 G 1
1 E 1	1 G 1

Worksheet: Sheet2
You say….." compare each complete row of sheet2 with sheet3 each complete row" … here is part of Sheet3 from your file:

1 L 1	1 E 1	1 L 1,1 L 1	1 G 1
1 G 1	1 E 1	1 L 1	1 L 1	1 G 1
1 L 1	1 L 1,1 L 1	1 L 1	1 G 1
1 L 1	1 L 1,1 L 1
1 L 1	1 G 1	1 E 1	1 L 1
1 G 1	1 L 1	1 L 1,1 L 1	1 L 1	1 L 1	1 G 1

You say … "compare each complete row of sheet2 with sheet3 each complete row and if sheet2 complete row match with sheet3.."…
OK so far so good, we see a match :) - ( 1 L 1 __ 1 L 1,1 L 1 = 1 L 1 __ 1 L 1,1 L 1 )

But here is the problem: You then say : " compare each complete row of sheet2 with sheet3 each complete row and if sheet2 complete row match with sheet3 complete row then highlight it with green colour in sheet4 in column B"…..
This is your Sheet4:

20abc
abc20
def
ghi

Worksheet: Sheet4
???
Are you attempting to say this:
Compare each complete row of sheet2 with each complete row of sheet3
If sheet2 complete row matches with sheet3 complete row then:
Highlight the row in sheet4 in column B corresponding to the row matched from Sheet2.

So in the shown example, the matched rows were row 1 in Sheet2 and row 4 in Sheet3.
So the column B row 1 is to be highlighted as the match from Sheet2 was in row 1

Please confirm that this is your requirement, or try to explain again
Thanks
Alan
:)

P.S.
If I have guessed your requirement correctly, then I have another question:
Question on you: Is there a maximum number of columns that may be used. ?

For the greater glory of God, I will answer the Thread anyway, and assume my guess on the requirement is correct. :)

Basic strategy:
I will look first at the data in both worksheets, Sheet2 and Sheet3 to determine the maximum number of rows and columns. I will use the VBA Range.Find Method as this will allow me to look by rows and columns and return the first row or column with something in it. The Range.Find method will return me the furthest down or along cell used. The way I use the Range.Find will be to look back from way down or across the worksheet and return me the first cell used, from which I can obtain the row or column property. That last row and column info will be used to determine my ranges of interest, as well as giving me a maximum column based on the largest from the two Worksheet data ranges which will be useful to know to limit the considered data.
I will use a mixture of array and worksheet range techniques to get arrays containing a string for each row consisting of all data in that row up to the maximum column. Those arrays will be used in a simple comparison, whereby I loop through each row in Worksheet Sheet1 data and look for the string in the array from the Worksheet 2

Note:
_1 This code will not consider duplicates
_2 I removed the comment ( we have to compare each complete row of sheet2 with sheet3 each complete row and if sheet2 complete row match with sheet3 complete row then highlight it with green colour in sheet4 in coloumn B ) which was in cell F13 of Worksheet 2 of the uploaded file, as this would be found on the Range.Find search and give a false value for the last column and row in Worksheet Sheet2
_3 I assume that the first row and first column can be used in the data range used
_._______________

So , I wrote a code. I used the test data in the uploaded file in Post #1. Here is that test data in full: http://www.excelfox.com/forum/showth...0765#post10765
The results are also shown there after running the code given below

_.___________________

Alan


Code:

Code:

---

Option Explicit
Sub GoGreen() ' http://www.excelfox.com/forum/showthread.php/2278-Compare-each-complete-row-of-sheet2-with-sheet3-each-complete-row
Rem 1 Worksheets info and data ranges
Dim Ws2 As Worksheet, Ws3 As Worksheet, Ws4 As Worksheet
 Set Ws2 = ThisWorkbook.Worksheets("Sheet2"): Set Ws3 = ThisWorkbook.Worksheets("Sheet3"): Set Ws4 = ThisWorkbook.Worksheets("Sheet4")
'1b last rows and columns using Range.Find Method : https://msdn.microsoft.com/de-de/vba/excel-vba/articles/range-find-method-excel
Dim Lr2 As Long, Lr3 As Long, Lc2 As Long, Lc3 As Long
 Let Lr2 = Ws2.Cells.Find(What:="*", After:=Ws2.Range("A" & Rows.Count & ""), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=True).Row
 Let Lr3 = Ws3.Cells.Find(What:="*", After:=Ws3.Range("A" & Rows.Count & ""), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=True).Row
 Let Lc2 = Ws2.Cells.Find(What:="*", After:=Ws2.Cells.Item(1, Columns.Count), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=True).Column
 Let Lc3 = Ws3.Cells.Find(What:="*", After:=Ws3.Cells.Item(1, Columns.Count), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=True).Column
'1b(ii) determin maximum column used in either data Worksheets
Dim Lc As Long: Let Lc = Lc2: If Lc3 > Lc2 Then Let Lc = Lc3
'1c data ranges
Dim Rng2 As Range, Rng3 As Range
 Set Rng2 = Application.Range(Ws2.Range("A1"), Ws2.Cells.Item(Lr2, Lc)): Set Rng3 = Application.Range(Ws3.Range("A1"), Ws3.Cells.Item(Lr3, Lc))
Rem 2 Build arrays for checking strings of complete row data
Dim arrSht2Chk() As String, arrSht3Chk() As String
ReDim arrSht2Chk(1 To Lr2): ReDim arrSht3Chk(1 To Lr3)
 Dim Cnt As Long
'2a arrSht2Chk()
    For Cnt = 1 To UBound(arrSht2Chk())
    Dim RngTmp As Range
    Set RngTmp = Application.Index(Rng2, Cnt, 0) ' range array 1 breadthJPG : https://imgur.com/IJgKBi3
    Dim StrTmp As String, Clms As Long
        For Clms = 1 To Lc
        Let StrTmp = StrTmp & RngTmp.Item(Clms)
        Next Clms
    Let arrSht2Chk(Cnt) = StrTmp
    Let StrTmp = ""
    Next Cnt
'2b arrSht3Chk()
    For Cnt = 1 To UBound(arrSht3Chk())
    Set RngTmp = Application.Index(Rng3, Cnt, 0) '
        For Clms = 1 To Lc
        Let StrTmp = StrTmp & RngTmp.Item(Clms)
        Next Clms
    Let arrSht3Chk(Cnt) = StrTmp
    Let StrTmp = ""
    Next Cnt
Rem 3 Main Loop to check for match and colour column B in Worksheet Sheet4
    For Cnt = 1 To UBound(arrSht2Chk())
    Dim MtchRes As Variant
    Let MtchRes = Application.Match(arrSht2Chk(Cnt), arrSht3Chk(), 0)
        If Not IsError(MtchRes) Then
        Let Ws4.Range("B" & Cnt & "").Interior.Color = 5287936
        Else
        End If
    Next Cnt
End Sub

---

Cross Post
http://www.eileenslounge.com/viewtopic.php?f=30&t=30447
http://www.eileenslounge.com/viewtop...=30447#p242902
http://www.excelfox.com/forum/showth...ll=1#post10839
http://www.excelfox.com/forum/showth...ll=1#post11187

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://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6BSa173Z
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa6-64Xpgl
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ms39yjd
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa5ZXJwRCM
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4Pr15NUt
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABa4I83JelY
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg. 8mjgPNoTt_HABa3tnAjhZU
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg. 8mjgPNoTt_HABa3KswxL3c
https://www.youtube.com/watch?v=suUqEo3QWus&lc=UgyBXFxnVWT3pqtdqPx4AaABAg
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABZ8N9O-O8p
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg. 8xzeMdC8IOGABZ8N9O-O8p
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgxREWxgx2z2Lza_0st4AaABAg
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgyikSWvlxbWS24NBeR4AaABAg
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgwNiH4hhyrd2UjDK8d4AaABAg
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg. 8mjgPNoTt_HAAf952WoUti
https://www.youtube.com/watch?v=hz4vb48wzMM&lc=Ugy2N3gvXBNrvWpojqR4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hello rider@1234
Welcome to excelfox , :)

Your description reads very short and concise. It reads to me somewhat like an Exam or Homework question.
In a forum when you are asking for help, it is sometimes better to try and give a bit more detail as to where your problem lies. Generally volunteers here try to help when you have a problem, rather than do free work.
Because of the short detail that you have given , I personally cannot really understand exactly what you want.

But I will make some attempt to give you some helpful directions.

Macro recorder
If you do not already know about the macro recorder, then you should search the internet for some tutorials on it.
The macro recorder allows you to get the coding to perform actions that you do manually: You start the macro recorder, then perform manually some actions. The macro recorder will then give you the coding needed to repeat those manual actions.
Even some experienced VBA users will often use this method to get the correct syntax. VBA is so complex, that many people will not have the time to either document or learn all syntax.

In your example, you are wanting to open / save / close files of different types.
Here, http://www.excelfox.com/forum/showth...1184#post11184 , I have done a macro recording example to get some of the coding which I may need to answer your question
I walk through some manual steps after the macro recorder is running, then stop the recorder , then show the recorded coding


Based on the coding there, and on this from you : .._
.....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 ...... ,

_.. I can now make some attempt at doing something like you want


Here is some coding to get you started … working on these files:
http://www.excelfox.com/forum/showth...ll=1#post11185
Those two files are closed initially


If you run the code below, then you will see that the highlighting in row 4 in file 1.xls is removed, because a match in the letter d was found.
I expect this may not be doing exactly what you want. But if you try to follow and understand all that I have done for you, then you may be able to modify the code to do exactly what you want

Code:

Code:

---

Option Explicit
Sub Removed_highlighted_colour_based_on_condition_1() ' http://www.excelfox.com/forum/showthread.php/2340-Removed-highlighted-colour-based-on-condition
' Data files are on the desktop. So is the this workbook , the macro file, "MainMacroFile.xlsm". So we can use the path to ThisWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\1.xls" ' "C:\Users\Elston\Desktop\1.xls"
' If column R of 1.xls file is not in minus
Dim Cnt As Long
    For Cnt = 1 To 10
        If Workbooks("1.xls").Worksheets.Item(1).Range("R" & Cnt & "").Value < 0 Then End ' Thius will stop the macro if any negative values are found
    Next Cnt
' if we get this far then no values in column R are in minus, so open 2.xlsx
Workbooks.Open Filename:=ThisWorkbook.Path & "\2.xlsx" ' "C:\Users\Elston\Desktop\2.xlsx"
' match column E of 1.xls with column A of 2.xlsx  ,  and if it matches
    For Cnt = 1 To 4
        If Workbooks("1.xls").Worksheets.Item(1).Range("E" & Cnt & "").Value = Workbooks("2.xlsx").Worksheets.Item(1).Range("A" & Cnt & "").Value Then
        'MsgBox prompt:="Match in row " & Cnt
        '  look for any highlighted colour in that row and if any highlighted cell in that row is found then remove the highlighted colour
            If Workbooks("1.xls").Worksheets.Item(1).Range("E" & Cnt & "").Interior.Color = 65535 Then
                With Workbooks("1.xls").Worksheets.Item(1).Range("E" & Cnt & "").Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
                End With
            Else
            End If
        Else
        End If
    Next Cnt
' save the file
Workbooks("1.xls").Save
' and close all the file
Workbooks("1.xls").Close
Workbooks("2.xlsx").Close
End Sub

---

Before running:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					a													1
2					b													2
3					c													3
4					d

Worksheet: Tabelle1

After running
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					a													1
2					b													2
3					c													3
4					d
5

Worksheet: Tabelle1


This is the other data file which I used for the example
_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B
1
2
3
4	d
5

Worksheet: Tabelle1


Alan

---

The two data files shown above are attatched at this post: http://www.excelfox.com/forum/showth...ll=1#post11185

The main file with the macros in it , "MainMacroFile.xlsm" , is attatched below.
( all files should be saved at the same place, then run the macro Sub Removed_highlighted_colour_based_on_condition_1() , which is in the main macro file )

cross posts
https://www.eileenslounge.com/viewto...p?f=30&t=32730
https://chandoo.org/forum/threads/co...-colour.41931/
http://www.excelfox.com/forum/showth...ll=1#post10839

Avinash Singh
https://excelfox.com/forum/showthrea...ll=1#post14092

Eileen’s Lounge.
First Leonardo1234 post was at end of July 2018
http://www.eileenslounge.com/viewtopic.php?f=30&t=30447
Hans got fed up around May/June 2019
http://www.eileenslounge.com/viewtop...252759#p252759
http://www.eileenslounge.com/viewtop...252961#p252961
roger@/umpsbug/leonardo1234/c@1234
zyxw123 started 22 April 2020

excelguru.ca
leonardo1234
https://www.excelguru.ca/forums/show...h-complete-row

Ozgrid
jhas56788
https://www.ozgrid.com/forum/index.p...38#post1234138
https://www.ozgrid.com/forum/index.p...38#post1234938


VBAExpress
KAJAL1234
http://www.vbaexpress.com/forum/show...ht=#post402805

excelforum
dumdumbum MoldyBread
https://www.excelforum.com/excel-pro...ml#post5346727

Chandroo
r@1234 Leonardo1234 ANJUS
https://chandoo.org/forum/threads/co...4/#post-253302
https://chandoo.org/forum/threads/vb...-2#post-264459

ExpertsExchange
https://www.experts-exchange.com/que...-the-file.html

---

Some of the known usernames. There are very likely a lot more. Both at English speaking forums and at other language speaking forums, such as Hindi
zyxw123
jhas56788
rider
roger
anjus
shinshan
sumanjjj
Leonardo1234
umpsbug
Kinjals
Tinamishra
kinjal124
sumanjjj123
KAJAL1234
rider1234
r@1234
ANJUS
andy124
rider@1234
fixer
c@1234
vixer
roger@
Leonard
dumdumbum
Ava453644
MoldyBread


Ava453644 awatesunil95@gmail.com 77.111.246.17 June 12 2020 register
103.216.212.90 14 June 2020 looking at fixer stuff after 3rd fixer ban
shiv1823@gmail.com 49.36.49.130 June 14
103.216.212.90

3 oct 2019, 07:40 PM #1
fixer The IP Address is: 103.216.212.92
The host name is: 103.216.212.92
PM: ( http://www.excelfox.com/forum/privat...=newpm&u=12517 )

07-28-2018, 07:22 PM #1
Leonardo1234 singhavinash.201351@gmail.com User: Leonardo1234 (id: 11895)
The IP Address is: 103.216.212.154
The host name is: 103.216.212.154

Fixer suryabhoir121@gmail.com User: fixer (id: 12517)
Sonntag 6 oct 103.216.212.92
The host name is: 103.216.212.92
The IP Address is: 103.216.212.93
The host name is: 103.216.212.93
Mumbai
maharashtra


Bro Subodh is no longer to help me bcoz expertsexchange has started the plans to login and ask question
HansV is not ready to help me
now u r the only hope for me just be with me for 10 question and believe me and my work will be completed bro…….

Most recent acive as dumdumbum at excelforum
:

Avinash Singh
Mumbai
Hauptstadt des Bundesstaates Maharashtra in Indien

See the attached files
Vba is placed in a seperate file
Open 1.xls file
Calculate the data add all the data in column H and paste the result in K9 of vba contained file in sheet1
and close the 1.xls file
sample files are attached below plz have a look sir