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:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
You have my_range delcared, but it's not in the rest of the code !
Also, what is Selected ??
You use Selection a number of times with no reference to a Selected range ?
And I don't see any reference to G2 down ?
 
Upvote 0
Hi Michael

Sorry for not getting back to you sooner as i have been on R&R.

My guess is that I have messed up the recorded macros while trying to work on solutions & piecing this macro together. I noticed that the "Parts Not Ordered" was in the wrong spot so I fixed that.

I am trying to conditional format the data in cells of column F2 not G2 (my mistake).

I have a suspicion that I should separate the indexing from the conditional formatting from the indexing somehow.

Regards,

Nathan
 
Upvote 0
Hi Nathan
As a suggestion, try recording "bits" of the task and get them right.
Then you can clean up the code and either combine the whole lot
OR
Call the other recorded macros into the main macro.
Yould vene repost the "bits" for us to help clean them up !!...(y)
 
Upvote 0
Michael

Will give it a try, however as previously discussed if I use something like Ctrl+Shft+down, to select data only in a column

I returns

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
End Sub

I thought we wanted to avoid using .Select
 
Last edited by a moderator:
Upvote 0
And as I said....you record this stuff....and we help clean it up....(y)

WE wanted to learn !!!

Code:
Sub Macro1()
dim lr as long
lr = cells(rows.count,"F").end(xlup).row
Range("F2:F" & lr).Select
End Sub

Then once you decide what you want to do with Range("F2:F" & lr), we can change the code again !!
You will get the hang of it !!!
 
Upvote 0
Michael, ok thanks with you now.

So now the code looks like this

Code:
'   References job numbers to actual backlogs to give desrciptions then conditional formats them based on backlog 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
    Dim lr 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

However I get a compile error in another section of the macro here, where the Dim lr As Long is below

Code:
This removes jobs that are closed in DBS & not in AMT backlogs. Potentialy abandoned or deleted workorders
    Dim lr As Long, i As Long
    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
    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
That last block of code works fine for me....but if lr as Long has already been declared somewhere else....you will get a compile error "Duplicate declaration"...if that's the case simply remove the later one
 
Upvote 0
I removed the last Dim lr As long & left i As Long but got another compile error.

Is was already declared in code in which was above the area where i got the error n last post

Code:
Dim lr As Long
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    Range("F2:F" & lr).Select
 
Upvote 0
What does the rest of the compile error message say ??
The same applies to all Dim statements, you can only use each one once in a macro UNLESS you are ReDimming.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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