how to highlight columns in another excel by using vba

[sivavuddanti]

Hi all,

I need small help I am running macro on one workbook (macro book ) for another workbook ( Raw data work book ) but I am getting some issue in middle , that macro is not working for raw data work book suddenly its going to macro book and highlighting the columns but I want to highlight the columns of raw data work book by using macro workbook can any one help me ........

 

[Joe4]

Welcome to the Board!

One way is to add code that activates your other workbook before running the commands. If you turn on the Macro Recorder, and record yourself moving between the two workbooks, you can see exactly what that code needs to look like.

If you still have issues, please post your code and the actual names of your workbooks here.

 

[sivavuddanti]

I understand what you said but if suppose next day the data will get change with days , so in my point of view that's not possible with record macro

for example

today I got data as like below
[TABLE="width: 75"]
<tbody>[TR]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]10/26/2017[/TD]
[/TR]
[TR]
[TD]10/27/2017[/TD]
[/TR]
[TR]
[TD]10/31/2017[/TD]
[/TR]
[TR]
[TD]10/16/2017[/TD]
[/TR]
[TR]
[TD]10/24/2017[/TD]
[/TR]
[TR]
[TD]10/25/2017[/TD]
[/TR]
[TR]
[TD]10/30/2017[/TD]
[/TR]
[TR]
[TD]10/17/2017[/TD]
[/TR]
[TR]
[TD]10/18/2017[/TD]
[/TR]
[TR]
[TD]10/19/2017[/TD]
[/TR]
[TR]
[TD]10/20/2017[/TD]
[/TR]
[TR]
[TD]10/23/2017[/TD]
[/TR]
[TR]
[TD]09/18/2017[/TD]
[/TR]
[TR]
[TD]11/01/2017[/TD]
[/TR]
[TR]
[TD]10/09/2017[/TD]
[/TR]
[TR]
[TD]10/10/2017[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

may be tomorrow
I can get different dates

so can any one suggest me to solve that problem

 

[Joe4]

Yes, recording macros is very literal. But it also gives you a really good start.
If you record the macro that does what you want, and post the code here, we can help you change it to work dynamically (regardless of days).

 

[sivavuddanti]

Hi can you please find the below code of recorded macro

Sub Macro2()
'
' Macro2 Macro
'
'
Range("I1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$1086").AutoFilter Field:=9, Criteria1:="ABC"
ActiveSheet.Range("$A$1:$I$1086").AutoFilter Field:=7, Criteria1:=Array( _
"10/27/2017", "10/30/2017", "10/31/2017", "11/01/2017"), Operator:= _
xlFilterValues, Criteria2:=Array(0, "11/2/2017")
ActiveWindow.SmallScroll Down:=-12
Range("A7:I7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$I$809").AutoFilter Field:=7
Range("G1").Select
End Sub

 

[sivavuddanti]

Hi bro actually how to highlight columns in another excel by using vba problem got solved but now I am searching for to filter last 5 business days from today’s date
above code is for that only after recording the macro.

 

[Joe4]

how to highlight columns in another excel by using vba problem got solved

How/where did it get solved?
Did you post it elsewhere?

 

[sivavuddanti]

Sub com()
Columns("A:B").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Columns("D:D").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Columns("G:G").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Columns("I:I").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
End Sub