Conditional Formatting in a Macro

p45cal · #11 05-15-2018, 10:00 AM

Your LOOKUP formula is not necessarily a good choice, it's a legacy function and needs the lookup table to be in the right order. I see it's not in Macro3, however that formula isn't quite right vis-a-vis the absolute/relative references. The attached contains the right formula in Macro3.
Macro3 has been streamlined. It also calls the AddPivot macro at the end.
For a REPORT sheet that's already got the data that your original Macro3 left there, there's a button to add a pivot table. It's a very, very basic pivot table.

Code:

Code:

Option Explicit
Sub Macro3()
Dim lr As Long
Range("A:X,Z:Z,AC:AI").Delete
Range("B:B").ClearContents
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
      :="-", FieldInfo:=Array(Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True
Range("A:A").Delete
Rows(1).Insert
Range("A1:C1").Value = Array("DEPT", "AMT", "PLANT")
Range("A:C").HorizontalAlignment = xlCenter
lr = Application.Max(2, Cells(Rows.Count, "A").End(xlUp).Row)
With Range("C2:C" & lr)
  .FormulaR1C1 = "=VLOOKUP(RC1,TABLE!R1C1:R133C2,2,FALSE)"
  '.Value = .Value 'include this if you want to convert formulae results into plain values.
End With
AddPivot    'adds pivot table.
End Sub


Sub AddPivot()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("REPORT").Cells(1).CurrentRegion.Resize(, 3)).CreatePivotTable(TableDestination:= _
      Sheets("REPORT").Range("E1"))
  .RowAxisLayout xlTabularRow
  .PivotFields("PLANT").Orientation = xlRowField
  .AddDataField .PivotFields("AMT"), "Sum of AMT", xlSum
End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
conditional formatting macro	ewso	Excel Programming	2	10-05-2017 12:10 AM
Conditional formatting	bbutl027	Excel	15	12-05-2016 08:56 PM
Conditional Formatting Macro	grexcelman	Excel Programming	1	01-10-2015 04:45 PM
Conditional formatting that ignores other formatting rules	info_guy2	Excel	1	07-03-2014 10:07 AM
Conditional Formatting and/or Macro for Character Insertion?	MonOverArchIng	Word	2	01-11-2013 02:48 AM