In support of this Thread in main Forums:
http://www.excelfox.com/forum/showth...d-on-condition
A macro recording example to get some coding to help answer this……
Originally Posted by
rider@1234
all files are located in desktop
vba is placed ........
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
Open Excel. ( I have Office 2007 ).
Turn on the macro recorder
Turn On Macro Recorder.jpg : https://imgur.com/uZkZWg1
( I use the default macro name and place for the coding to be stored )
Use default macro name and place where macro is stored.jpg : https://imgur.com/rR8UkT1
I open a new file.
Open new file.jpg : https://imgur.com/10pnrBL
( I am in Excel 2007, so I end up with a .xlsx file
New xlsx File.JPG : https://imgur.com/vvqEC1w
I save this file on the desktop, giving it the name "MainMacroFile"
Save As.jpg : https://imgur.com/PrnZhAW
Save As xlsx.JPG : https://imgur.com/PrnZhAW
I resave as a file to hold macros:
Save As xlsm.JPG https://imgur.com/5hVZAld
I open a new file
Open new file.jpg : https://imgur.com/vvqEC1w
( I am in Excel 2007, so I end up with a .xlsx file
New xlsx File.JPG : https://imgur.com/vvqEC1w
I save this file on the desktop, giving it the name "1"
Save As.jpg : https://imgur.com/PrnZhAW
Save As 1_xlsx.JPG https://imgur.com/y2uMg0i
I resave as a 1.xls
Save As.jpg : https://imgur.com/PrnZhAW
Save As 1_xls.JPG : https://imgur.com/iylw8r7
I close the file "1.xls"
Close 1_xls.jpg : https://imgur.com/vJtdLHo
I open the file "1.xls"
Open 1_xls.jpg : https://imgur.com/de2MGkt
Open 1_xls.jpg : https://imgur.com/zafLOTd
I now take some actions, …. which are similar to your …. 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 …..
What I actually did was
_ Put some positive numbers in column R
_ Put some letters in column E
_ Put some highlight in column E
Add some numbers letters and highlighting.JPG : https://imgur.com/6ZYeaxU
_ I take some of the highlighting off
Take some highlighting off.jpg : https://imgur.com/Z1Rk9HD
I save the file "1.xls"
Save 1_xls.jpg : https://imgur.com/FmLrhjZ
I close the file "1.xls"
Close 1_xls.jpg : https://imgur.com/R6eqv1m
I make a new file, "2.xlsx" , on the desktop
New xlsx File.JPG : https://imgur.com/vvqEC1w
Save As 2_xlsx.JPG : https://imgur.com/mHacZ74
I close the file and open it
Close 2_xlsx.jpg : https://imgur.com/tReoAJG
Open 2_xlsx.jpg : https://imgur.com/jmsqNY1
Open 2_xlsx.jpg : https://imgur.com/J3XPhz5
I put a letter in column A of "2.xlsx"
Put a letter in column A of 2_xlsx.JPG : https://imgur.com/9o8PTYQ
I save the file "2.xlsx"
Save 2_xlsx.jpg : https://imgur.com/ehSxG6b
I close the file "2.xlsx"
Close 2_xlsx.jpg : https://imgur.com/YyRmhfW
I now stop the macro recorder
Stop macro recorder.JPG : https://imgur.com/NrcDQS4
Stop macro recorder.JPG : https://imgur.com/WRgy3mB
Find the macro just recorded and click on Step Into
Find macro just recorded.JPG : https://imgur.com/xK3abHT
Find macro just recorded.JPG : https://imgur.com/34U5nm3
( Alternatively hit Alt+F8 )
You can then see the recorded macro
Recorded Macro.JPG : https://imgur.com/Mo0vfHi
I include the code below, but have also added some 'Comments ( ' Rems) to show the steps which I manually made above
Code:
Sub Makro1()
'
' Makro1 Makro
'
'
' Rem I open a new file.
Workbooks.Add
' Rem I save this file on the desktop, giving it the name "MainMacroFile"
ChDir "C:\Users\Elston\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Users\Elston\Desktop\MainMacroFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
' Rem I resave as a file to hold macros
ActiveWorkbook.SaveAs Filename:="C:\Users\Elston\Desktop\MainMacroFile.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' Rem I open a new file.
Workbooks.Add
' Rem I save this file on the desktop, giving it the name "1"
ActiveWorkbook.SaveAs Filename:="C:\Users\Elston\Desktop\1.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
' Rem I resave as a 1.xls
ActiveWorkbook.SaveAs Filename:="C:\Users\Elston\Desktop\1.xls", FileFormat _
:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
' Rem I close the file "1.xls"
ActiveWorkbook.Close
' Rem I open the file "1.xls"
Workbooks.Open Filename:="C:\Users\Elston\Desktop\1.xls"
' Rem I now take some actions, …. ....... 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
Range("R1").Select
ActiveCell.FormulaR1C1 = "1"
Range("R2").Select
ActiveCell.FormulaR1C1 = "2"
Range("R3").Select
ActiveCell.FormulaR1C1 = "3"
Range("E1").Select
ActiveCell.FormulaR1C1 = "a"
Range("E2").Select
ActiveCell.FormulaR1C1 = "b"
Range("E3").Select
ActiveCell.FormulaR1C1 = "c"
Range("E4").Select
ActiveCell.FormulaR1C1 = "d"
Range("E1:E4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' Rem _ I take some of the highlighting off
Range("E2").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("E3").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' Rem I save the file "1.xls"
ActiveWorkbook.Save
' Rem I close the file "1.xls"
ActiveWorkbook.Close
' Rem I make a new file, "2.xlsx" , on the desktop
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\Users\Elston\Desktop\2.xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
' Rem I close the file and open it
ActiveWorkbook.Close
Workbooks.Open Filename:="C:\Users\Elston\Desktop\2.xlsx"
' Rem I put a letter in column A of "2.xlsx"
Range("A4").Select
ActiveCell.FormulaR1C1 = "d"
Range("A5").Select
' Rem I save the file "2.xlsx"
ActiveWorkbook.Save
' Rem I close the file "2.xlsx"
ActiveWorkbook.Close
' Rem I now stop the macro recorder
End Sub
Reply With Quote
Bookmarks