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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This is the other data file which I used for the example
_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
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 )
Reply With Quote
Bookmarks