VBa issue

Barrakooda

Board Regular
Joined
Feb 3, 2012
Messages
75
Hi all

Does anyone know how to write this better, worked ok yesterday. Today it doesn't conditional format G2 down, index's fine.
Code:
'   References job numbers to actual backlogs to give desrciptions then conditional formats them based on backlog status
    Dim my_range As Range
     With ActiveSheet
          Range("E2", Range("E" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),6)"
     With ActiveSheet
          Range("F2", Range("F" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),14)"
    Selection.FormatConditions.Add Type:=xlTextString, String:="Job Ready", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="Not Ready", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:= _
        "Parts Not Ordered", TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
 
Last edited by a moderator:
Michael

Its all good, put the Dim further u in the code (before formatting of Job Status)& everything seems to be working now. Thanks again for your assistance.

Whole macro as it stands now

Code:
Sub BacklogListSort()
'   Auto fits evrything to column
    Cells.EntireColumn.AutoFit
'   Gets rid of jobs with NONE in them
    With ActiveSheet
    .AutoFilterMode = False
    With Range("E2", Range("E" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*(NONE)*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
    End With
'   References job numbers to actual backlogs to give desrciptions & then references backlogs to give job status
    With ActiveSheet
          Range("E2", Range("E" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),6)"
    With ActiveSheet
          Range("F2", Range("F" & Rows.Count).End(xlUp)).Formula = "=INDEX('Current Backlogs'!A$1:T$2000,MATCH(B2,'Current Backlogs'!L$1:L$2000,0),14)"
    End With
'   Conditional formats job status
    Dim lr As Long, i As Long
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    Range("F2:F" & lr).Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Job Ready", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="Not Ready", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="Parts Not Ordered", _
         TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End With
'   Sort dates oldest to newest and then conditional formats them based on older than 30 60 90 days
    Range("G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:= _
        Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A2:L1000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=TODAY()-30"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=TODAY()-60"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=TODAY()-90"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'   Adds currency to column D and puts description in header
    Columns("D:D").Style = "Currency"
    Range("E1").Value = "Description"
'   Adds table to sheet
    With ActiveSheet
        .UsedRange
        .ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table2"
    End With
'   This removes jobs that are closed in DBS & not in AMT backlogs. Potentialy abandoned or deleted workorders
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
    If IsError(Range("E" & i)) And Range("L" & i) = "CLSD" And Range("K" & i) = "FALSE" Then Rows(i).Delete
    If IsError(Range("E" & i)) And Range("L" & i) = "CLSD" And Range("K" & i) = "TRUE" Then Rows(i).Delete
    If IsError(Range("E" & i)) And Range("L" & i) = "" And Range("K" & i) = "FALSE" Then Rows(i).Delete
    If IsError(Range("E" & i)) And Range("L" & i) = "" And Range("K" & i) = "TRUE" Then Rows(i).Delete
    Next i
    Application.ScreenUpdating = True
'   This hides Columns C H I J so its easier for printing
    Range("C:C,H:J").EntireColumn.Hidden = True
End Sub
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,215,503
Messages
6,125,179
Members
449,212
Latest member
kenmaldonado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top