macro for sort and highlight

[IKHAN]

hi,


Using macro below to sort and highlight,Having trouble with macro below :


1. Need to highlight entire row with color if matching numbers in column A

2. Need to highlight entire row with different color if matching number in column A

3. Need to highlight font only if column D(dates) and Column E(dates) if dates dont match.


Sub Format_Data()


Dim LR As Long


'Delete top 3 rows

Rows("1:3").Delete Shift:=xlUp


Cells.Select

Selection.UnMerge


'Find Last Row No.

LR = Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Columns("A:A").ColumnWidth = 12

Columns("B:B").ColumnWidth = 11.29

Columns("C:C").ColumnWidth = 10.86

Columns("D:D").ColumnWidth = 18.86

Columns("D:D").ColumnWidth = 23.14

Columns("E:E").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 23.14

Columns("F:F").Select

Selection.Delete Shift:=xlToLeft

Selection.Delete Shift:=xlToLeft

Columns("G:G").ColumnWidth = 15.57

Columns("G:G").ColumnWidth = 8.43

Columns("H:H").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 13

Selection.ColumnWidth = 7.57

Columns("I:I").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 16.14

Columns("J:J").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 22.71

Columns("K:K").Select

Selection.Delete Shift:=xlToLeft

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 6

Columns("L:L").ColumnWidth = 18.29

Range("L2").Select

Columns("L:L").ColumnWidth = 23.86

Columns("M:M").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 19.14

Selection.ColumnWidth = 30.71

Selection.ColumnWidth = 39

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 12

Columns("N:N").Select

Selection.Delete Shift:=xlToLeft

Columns("O:O").Select

Selection.Delete Shift:=xlToLeft

Columns("P").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 18.71

Columns("O:O").ColumnWidth = 19

Columns("Q:Q").Select

Selection.Delete Shift:=xlToLeft

Selection.ColumnWidth = 16.71

Columns("R:R").Select

Selection.Delete Shift:=xlToLeft

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

Selection.ColumnWidth = 23.43

Selection.ColumnWidth = 24.86

Columns("S:S").Select

Selection.Delete Shift:=xlToLeft

Columns("U:U").Select

Selection.Delete Shift:=xlToLeft

Columns("T:T").ColumnWidth = 38.29

Columns("T:T").ColumnWidth = 58.29

Columns("S:S").ColumnWidth = 23.71

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

'Sort according to Rules

Range("A1").Select

With ActiveWorkbook.Worksheets("Daily RFC Report").Sort

.SortFields.Clear

.SetRange Range("A1:T" & LR)

.SortFields.Add Key:=Range("J2:J" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

.SortFields.Add Key:=Range("A2:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With


'Set freeze panes

Range("C2").Select

ActiveWindow.FreezePanes = True


'Add helper column & hide it

Range("U2").FormulaR1C1 = "=IF(RC[-18]<>R[-1]C[-18],NOT(R[-1]C),R[-1]C)"


Range("U2").AutoFill Destination:=Range("U2:U" & LR)

Columns("U:U").EntireColumn.Hidden = True


'Setup Conditional Formatting

Range("A2:T2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$U2=TRUE"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent6

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions(1).StopIfTrue = True


Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$U2=FALSE"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent5

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions(1).StopIfTrue = True


ActiveSheet.Range(Cells(2, "A"), Cells(LR, "T")).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=T2<>"""""

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Color = -16776961

End With

Selection.FormatConditions(1).StopIfTrue = True


Range("A1").Select


End Sub

 

[SirJB7]

Hi, IKHAN!

Could you please clarify what's your definition of "matching numbers in column A"?

Considered uploading a sample file? Otherwise at least worksheet columns definition (format, formulas, examples).

Regards!

 

[IKHAN]

SirJB7 , Trying to achieve below as i download file everyday and have to go throu sorting,Highlighting and clearing data, Can i get help to create macro to do this job.


-Delete First 3 rows


-Unmerge cells and delete empty columns


-Freeze pane C2 ( Header ROW and First 2 Columns)


Sort based on hearder file


1.Start Date(Column J) - Descending order(new date to old date)

2.Then sort based on Cloumn A (ID) (descending)

3.Highlight entire row upto column T with Color YELLOW if matching numbers found in column (A) (id) , subsequently to Check next set of matching numbers in coulmn A and highlight with alternate color.

4.Compare in each row modify date and create date - if date doesnt match


(highlight red and bold) just the changed dates.


Check date in create date(Coulmn E) delete complete row if year(2011) is found : FORMAT mm/dd/2011 9:01:07 AM OR Jul 11,2011


SAMPLE FILE DOWNLOADED :


https://docs.google.com/spreadsheet/ccc?key=0Ah8e7yppIv6NdHFXNE5vUmh6NzY1S3B5cTJQUXcwekE

 

[SirJB7]

Hi, IKHAN!


Give a look at this file:

https://dl.dropbox.com/u/60558749/macro%20for%20sort%20and%20highlight%20-%20sample%20fileJuly13%20%28for%20IKHAN%20at%20chandoo.org%29.xlsm


I think that your didn't post your original downloaded file in below part of sheet Sheet1, because of the first part of your macro with the deletion of columns. Upon that base, I built what I thought was your original file in sheet 'Download original'. Then I created sheet Parameters with the original columns of downloaded file and final widths, setting 0 for deleted rows, -1 for standard width and other values for manual set widths (columns in first row, widths in row 2, ... and row height in A4).


You'll find the macro as ReformatDownload in module Module1. Two issues pending:

a) change the definitions for Formula1 values (formulas for conditional formatting) from Spanish (my Excel version language) to English (I assume yours) which are commented at the end of those lines

b) check the conditional format code for 'unknown condition' since I can't find any correlation between your description in previous post and this piece of code.


For the rest, I took your code, structured it a bit, changed references to object variables for worksheets and ranges, and not much more.


Just advise if any issue.


Regards!

 

[IKHAN]

SirJB7 ,Tried running macro and wrong columns get highlighted ,I should have uploaded the original file for you to review.


Any ways have uploaded the original file and output file reqd.


Thanks for all your help!!!


Original file :


https://docs.google.com/spreadsheet/ccc?key=0Ah8e7yppIv6NdFpxSGVJRHNqUjV0bVk4c3lDMXRwbFE


Output file :


https://docs.google.com/spreadsheet/ccc?key=0Ah8e7yppIv6NdGpWN3lRbkZYc1pxX1VZLU5KSFJKZFE

 

[SirJB7]

Hi, IKHAN!


I'd have successfully graduated as a cryptographer, as all columns were rightly guessed in input from your posted VBA code.

If wrong columns got highlighted that was because of the mismatch between code and requirement descriptions (i.e., unknown condition code, etc.).


I now updated the uploaded workbook as to take input from another file (workbook "... - Orginal ... .xlsx", see proper constant within the code).


Download if from same previous link.


Regards!

 

[IKHAN]

Thank You SirJB7....Works like a charm.

Congrats on graduating as Cryptographer....Best of luck.

 

[SirJB7]

Hi, IKHAN!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!